Table of Contents 1 2 Overview.............................................................................................................6 1.1 Lehman Brothers – A Brief Introduction ...................................................................... 6 Lehman Oracle Database Environment Awareness ..............................................6 2.1 List of Oracle Database Servers ................................................................................... 6 2.2 How to get the host name for the Oracle Database? ................................................... 6 2.3 How to get the Role name for the Oracle Database? ................................................... 6 2.4 How to get the List of Servers on a Unix Host?............................................................ 6 2.5 What is Remedy System? ............................................................................................ 7 2.6 How are the Databases Monitored? ............................................................................. 8 2.7 How to Disable/Enable Monitoring? ............................................................................. 9 2.8 Autosys Job Monitoring ............................................................................................. 10 2.9 Primary and off hour update for US and UK............................................................... 10 2.10 BING/ BTELL significance and reaction required ........................................................ 12 2.11 Scripts and their awareness ...................................................................................... 12 2.12 OFA followed for US and UK Environments ................................................................ 14 2.13 When & How to Raise an LL Incident ........................................................................ 17 2.14 Escalation Procedure and Outage handling ................................................................ 17 Oracle Binary Installation Procedure ................................................................. 18 3.1 Installation procedure for 8i ...................................................................................... 18 3.2 Installation procedure for 9i ...................................................................................... 18 3.3 Installation Procedure on the Hosts across the Firewall ............................................. 26 3.4 Patch installation procedure ...................................................................................... 26 Database Creation ............................................................................................. 26 4.1 Database Creation on 8.1.7 ....................................................................................... 26 4.2 Database Creation on 9i ............................................................................................ 26 4.3 Database Creation with different Block size and Character Set .................................. 26 Lehmanising the new Oracle Databases ............................................................ 26 5.1 Setting up the Database Monitoring .......................................................................... 26 5.2 Setting up the Database Backup................................................................................ 27 5.3 Setting up the Performance Statistics ........................................................................ 29 5.4 Scripts & Jobs to be set up for the new Database environment. ................................ 30 5.5 In perspective of UK Environment ............................................................................. 30 5.6 Setting up the Database Password using getsap/putsap ............................................ 30 5.7 Updating the Database Contacts (ADB/dbcontact/banner)......................................... 36 Post DB Creation Tasks ..................................................................................... 37 6.1 Installing Oracle 9i JVM ............................................................................................. 37 6.2 How to install JVM(XDK) on 9i DB ............................................................................. 39 6.3 Reload the JVM in 9.2.0.X ......................................................................................... 46 Up gradation ..................................................................................................... 50 7.1 OPatch Installation .................................................................................................... 50 7.1.1 Procedure for OPatch installation .......................................................................... 50 7.1.2 Major Steps for Oracle 8174 Security Patch........................................................... 51 7.2 Up gradation ............................................................................................................. 52 7.2.1 Oracle Database Upgrade from 8.1.7.0 32Bit to 8.1.7.4 64Bit with Java installed .. 52 7.2.2 Procedure to migrate from Oracle 9i to Oracle 9.2.0.5 .......................................... 53 7.2.3 Procedure to Migrate from 8.1.7.4 to 9.2.0.3 ........................................................ 53 7.2.4 Upgrading the JAVA ENGINE (Only for upgrades to 9.0.1) .................................... 60 7.2.5 Case Study: Refresh and Migration to 9.2.0.3 ....................................................... 60 Database Maintenance Task .............................................................................. 61 8.1 Procedure to request SA to create raw partition - Template....................................... 61 8.2 Procedure to Add Space to the Tablespace................................................................ 63
3
4
5
6
7
8
8.2.1 Adding Tablespaces or Datafiles to the Primary Database ..................................... 63 8.3 Database schema setup ............................................................................................ 64 8.4 Database Analyze task (Manual/Automated) ............................................................. 65 8.5 Database Reindex (Manual/Automated) .................................................................... 66 8.6 Database Spacereport ............................................................................................... 67 8.7 Purgelog tasks .......................................................................................................... 68 8.8 Startup and Shutdown using oractl and other scripts and their configurations ........... 68 8.9 Procedure to setup the Top SQL report ..................................................................... 69 8.10 Procedure to setup the row count ............................................................................. 69 8.11 Procedure to manually synchronize to Standby Latency ............................................. 70 8.12 Procedure to purge statspack report ......................................................................... 71 8.12.1.1 Recreating Statspack ................................................................................ 71 8.13 Re-org Activity .......................................................................................................... 72 8.13.1 Procedure to identify the objects which need to be re-org ................................ 72 8.13.2 Identify the tablespaces (8i) which need to be re-org ....................................... 72 8.13.3 Procedure to re-org the tablespaces to defragmentation ................................... 72 8.13.4 Procedure to re-org the objects ........................................................................ 74 8.13.4.1 Reorganize the tables to get rid of chained rows ...................................... 74 8.13.5 NJEXPPRD Oracle Server Reorganization ........................................................... 80 8.13.6 Reorganization For NJTTPRD1 ......................................................................... 99 9 Database Administration Tasks ....................................................................... 105 9.1 Procedure to Refresh a Server................................................................................. 105 9.1.1 Post Refresh Tasks ............................................................................................. 106 9.2 To create otrace for OEM Diagnostic Pack : Trace Data Viewer ............................... 108 9.3 HPOV Monitor - Resource Usage ............................................................................. 109 10 Scripts ......................................................................................................... 109 10.1 System State Dumps ............................................................................................... 109 10.2 Database Startup Scripts ......................................................................................... 109 10.3 Sybase and Oracle Database Server Shutdown Scripts ............................................ 109 10.4 Determine the Process that consumes most CPU ..................................................... 110 10.5 Turn Auto Tracing on for SQL statements................................................................ 110 10.6 NJRAPPRD index Rebuild Script ............................................................................... 111 11 Backup and Recovery .................................................................................. 111 11.1 Procedure to take the Backup of the Database ........................................................ 111 11.2 How to restore the Backup of the Database on Development/Staging ..................... 111 11.3 Procedure to take the Export Backup of the Database ............................................. 113 11.4 Procedure to restore the Backup on the Different host on Different Directory Structure ............................................................................................................................. 114 11.5 Backup procedure for UK ........................................................................................ 115 11.6 Autoload procedure for UK ...................................................................................... 115 11.7 Net Backup ............................................................................................................. 119 12 Standby Database........................................................................................ 119 12.1 Procedure to Setup the Standby Database .............................................................. 119 12.2 Refreshing the Standby Servers .............................................................................. 129 12.3 Procedure when a datafile is added to primary ........................................................ 129 12.4 Procedure to manually Synchronize the Standby Latency ........................................ 131 12.5 Refresh of Standby DB with Primary DB(for Syncing of Standby with Primary) ........ 132 12.6 Syncing STANDBY Database - NJMERBAK ............................................................... 132 12.7 Troubleshoot........................................................................................................... 133 12.7.1 Standby Shutdown Recreation ........................................................................ 133 13 Application Database ................................................................................... 134 13.1 CAMEO ................................................................................................................... 134 13.1.1 Application Description ................................................................................... 134
13.1.2 Environment Details........................................................................................ 134 13.1.3 Special DBA Tasks .......................................................................................... 136 13.1.3.1 Steps for the CAMEO Host Reboot .......................................................... 136 13.1.3.2 CAMEO Replication Tricks ....................................................................... 137 13.1.3.3 Cameo Refresh....................................................................................... 139 13.1.3.4 Procedure to Sync Cameo Data .............................................................. 142 13.1.3.5 Commands ............................................................................................. 143 13.1.4 Troubleshooting.............................................................................................. 143 13.2 RAPTOR .................................................................................................................. 148 13.2.1 Application Description ................................................................................... 148 13.2.2 Environment Details........................................................................................ 149 13.2.3 Special DBA Tasks .......................................................................................... 149 13.2.4 Troubleshoot .................................................................................................. 150 13.3 Lehman Live ........................................................................................................... 150 13.3.1 Application Description ................................................................................... 150 13.3.2 Environment Details........................................................................................ 150 13.3.3 Special DBA Tasks .......................................................................................... 151 13.3.4 Troubleshoot .................................................................................................. 151 13.4 GTT ........................................................................................................................ 151 13.4.1 Application Description ................................................................................... 151 13.4.2 Environment Details........................................................................................ 151 13.4.3 Special DBA Tasks .......................................................................................... 151 13.4.4 Troubleshoot .................................................................................................. 152 13.5 NYESERV ................................................................................................................ 152 13.5.1 Application Description ................................................................................... 152 13.5.2 Environment Details........................................................................................ 152 13.5.3 Special DBA Tasks .......................................................................................... 152 13.5.3.1 NYESERV Support process ...................................................................... 152 13.5.4 Troubleshoot .................................................................................................. 153 13.6 ADB ........................................................................................................................ 154 13.6.1 Application Description ................................................................................... 154 13.6.2 Environment Details........................................................................................ 154 13.6.3 Special DBA Tasks .......................................................................................... 154 13.6.4 Troubleshoot .................................................................................................. 154 13.7 R2R ........................................................................................................................ 154 13.7.1 Application Description ................................................................................... 154 13.7.2 Environment Details........................................................................................ 154 13.7.3 Special DBA Tasks .......................................................................................... 154 13.7.4 Troubleshoot .................................................................................................. 154 14 Veritas Cluster SetUp................................................................................... 154 14.1 DBA part in cluster configuration. ............................................................................ 154 14.2 Cautions to be taken in handling Databases & Resources when it comes to VCS . 154 15 Shareplex .................................................................................................... 155 15.1 Shareplex Learning ................................................................................................. 155 15.1.1 Documentation available at............................................................................. 155 15.1.2 Important base file with basic commands ....................................................... 155 15.1.3 Table – depicting replication flow details for PROD,STAGE and QA Ecommerce environments .................................................................................................................. 156 15.1.3.1 Points to remember................................................................................ 156 15.1.4 Prime End User Contacts ................................................................................ 157 15.1.5 Checklist to verify the right ecommerce environment details with reference to Shareplex. 157 15.1.5.1 Inferences .............................................................................................. 158
15.1.6 A glimpse at the Shareplex directory Structures .............................................. 158 15.1.7 Sample contents of DIRs under $ SP_SYS_VARDIR ......................................... 159 15.1.7.1 Sample dump contents ........................................................................... 159 15.1.7.2 Sample log contents ............................................................................... 159 15.1.7.3 Sample contents of Config DIR ............................................................... 159 15.1.7.4 Bin DIR of Shareplex .............................................................................. 160 15.1.7.5 Data Directory of Shareplex.................................................................... 160 15.1.7.6 Frequently used/referred files in the data DIR ........................................ 161 15.1.7.7 Contents of oramsglist and it’s importance. ............................................ 161 15.1.7.8 Notes on some commands/logs Interpretation and Usage ...................... 162 15.1.8 Compare jobs and processes .......................................................................... 162 15.1.9 Information On queues ................................................................................... 162 15.1.10 Vendor contact details/procedure ................................................................... 163 15.1.11 Useful commands and hands on approach ...................................................... 163 15.1.12 Q&A Section ................................................................................................... 165 15.1.13 Troubleshooting Section ................................................................................. 165 15.1.13.1 Problem Message 1 ................................................................................ 165 15.1.14 About Shareplex compare process and procedure to sync out-of-sync tables in ecommerce environments ............................................................................................... 167 15.1.15 About Quest ................................................................................................... 167 15.1.15.1 Software Support Patch notifications ...................................................... 167 15.1.15.2 Support .................................................................................................. 168 15.1.16 About Shareplex Production cron entries ......................................................... 168 15.1.17 About Ecommerce User Group Notification Procedures .................................... 168 15.1.18 About Script to confirm Shareplex replication status ........................................ 169 15.1.19 About GIS Alert: Patch Application on Production ............................................ 169 15.1.20 About few tips on checking if activation of config is complete ......................... 172 15.2 Installation of Binaries? ........................................................................................... 172 15.3 Replication Set up? ................................................................................................. 172 15.4 Ecomm Release Work plan for Production , Staging & QA ....................................... 172 15.5 Logging a case with quest ....................................................................................... 172 15.6 How to resolve: STAGE Shareplex compare job reported the following tables are outof-sync 172 15.7 How to kill only Compare Shareplex process on the Source System ......................... 174 15.8 What request should be placed to SA to kill the process owned by root? ................. 175 16 Troubleshooting .......................................................................................... 176 16.1 When ORA-600/ ORA-7445 Error occurs.................................................................. 176 16.2 When 1652 Error occurs/ Tablespace Fills up .......................................................... 176 16.3 When archive log dest fills up.................................................................................. 177 16.4 When database hangs ............................................................................................. 177 16.5 When Host/ Databases crashes ............................................................................... 177 16.6 Recovering from deleted data ( flashback / Logminor) ............................................ 177 17 Tips ............................................................................................................. 178 17.1 Consolidated procedure to login to all different type of hosts .................................. 178 17.1.1 DBU hosts (i.e. nydbupora0-m, nydbupora1-m) .............................................. 178 17.1.2 Login to any box (or bo-xsrv) ......................................................................... 178 17.1.3 On HR hosts (i.e. njhrprd1)............................................................................. 178 17.1.4 On the hosts across firewall (i.e. njovmlmprd1, nyovmlmprd2) ...................... 178 17.2 How to Read Core Files ........................................................................................... 178 17.3 How to generate explain for SQLs ........................................................................... 181 17.4 Explain plan Add on ................................................................................................ 181 17.5 To check memory used by pinned SQLs .................................................................. 182 17.6 Character set .......................................................................................................... 182
17.7 Use LogMiner to Extract SQL Redo for DML Against Dropped Table ......................... 183 17.8 Accessing Remedy via Command line ...................................................................... 191 17.9 Kerberos login notes ............................................................................................... 191 18 Miscellaneous .............................................................................................. 191 18.1 Procedure to Update London Database Users Contact ............................................. 191 Appendix A: Obsolete Parameters in 9i: .................................................................. 192
1
Overview
1.1 Lehman Brothers – A Brief Introduction
Lehman Brothers is a global investment bank serving the financial needs of corporations, institutions, governments and high net worth investors worldwide. Lehman Brothers actively participates in the global capital markets through a closely integrated network of offices anchored by a worldwide headquarters in New York and regional headquarters in London and Tokyo. Since Lehman Brothers was founded in 1850, it has built a global reputation for providing research, distribution, trading and financing services. It depends on state-of-the-art IT infrastructure for providing its services. Lehman Brothers has the people, resources and knowledge necessary to serve institutional investors whenever and wherever the need for financing or investment opportunities arises.
2
Lehman Oracle Database Environment Awareness
2.1 List of Oracle Database Servers
Attached below is the list of Oracle Database Servers: List of Oracle Database Servers is attached in Appendix section of the manual
2.2
How to get the host name for the Oracle Database?
Use the command $ HostForServer e.g. nybo-xsrv1% HostForServer CAMEO njcameoprod1 Normally the path ( /opt/bin ) is set for all login’s, otherwise use /opt/bin/HostForServer
2.3
How to get the Role name for the Oracle Database?
Use the command $ RoleForServer e.g. nybo-xsrv1% RoleForServer CAMEO primary
2.4
Use the command $ ServerOnHost
How to get the List of Servers on a Unix Host?
e.g. nybo-xsrv1% ServerOnHost njcameoprod1
BL
Cluster Server
Host
Roles
Vendor
---------- --------- ------------------- --------------- ----------- -------BOB CAMEOORA CAMEO njcameoprod1 primary BOB BOB BOB BOB CAMEOSYB NJCAMEOPROD_RS njcameoprod1
ORACLE
repserver SYBASE openserv primary backsrv SYBASE SYBASE SYBASE
CAMEOSYB NJCAMEOSYB1_DIRECT njcameoprod1 CAMEOSYB NJCAMEOSYBPROD njcameoprod1
CAMEOSYB NJCAMEOSYBPROD_BCK njcameoprod1
2.5
What is Remedy System?
Remedy is an application through which users raise their requests. The requests are of two kinds. Help Desk requests - These can be user raised or from Monitoring system (FMS). Change Management Requests – These need the approval from Application managers and DBA managers for the activity.
Remedy, is a global distribution Help Desk ticketing system that allows to log both incidents and change requests for technology services and equipment supported within Lehman Brothers. The various components comprising Remedy is described below
2.5.1 Remedy Service Console
This is the main interface for the Remedy Help Desk application. The console works like a control panel from which users can perform their primary tasks. The console contains separate features for each of the roles defined in the Remedy Help Desk application.
2.5.2 Remedy Approval Request System
It is a client tool in which users enter and track requests through the resolution process. Users can also search the database, generate reports and modify existing requests with Remedy User.
2.5.3 Remedy Source
It is a drop down menu indicating the incident source when received by the Help Desk or second level support group. The source could be through one of the following Phone: User called the Help Desk; default incident source Email: Outlook template or email was used to request/submit incident case Web: HTML client was used to submit incident case FMS: Fault Management System, a monitoring systems that generates
automated tickets based on the severity of a fault that is being monitored Auto Generated: Ticket Automatically generated by an application
2.5.4 Remedy Status
The status of the remedy can be obtained from this menu.
New: First stage of the request cycle; case has been logged by the Help Desk or support group Assigned: Second stage of the request cycle; case has been assigned to a second level support group Work in Progress: Third stage of request cycle; individual has begun working on case On Hold Supplementary: Stage of request cycle; resolution has temporarily been delayed On Hold: Reason justification code for pending status (e.g., ordered) Resolved: Fifth stage of request cycle; second level support has corrected the incident Closed: Sixth stage of request cycle; second level support has confirmed resolution with user Note: Closed tickets cannot be re-opened, unless a request is submitted to a Remedy application administrator (mailto:Remedyhelp@lehman.com)
2.6
How are the Databases Monitored?
Once a new database is created , We have to make an entry into Datacompass ( Link to Datacompass procedure – to be created ) . The database users, hp_dbspi and gmonitor also have to be created.
(1). The tsnames.ora that the DB SPIs specifically look for is $ORACLE_HOME/network/admin/tsnames.ora. If not please create a link to the tnsnames.ora file. (2). $ORACLE_HOME is determined by the entry in the file /var/opt/oracle/oratab. Hence for all the Database servers the tnsnames.ora file should placed in the standard location ( $ORACLE_HOME/network/admin/tnsnames.ora). (3). The DBA s should always check the connection locally using the right ORACLE_HOME values. (4). Check for the login and passwd.
2.7
How to Disable/Enable Monitoring?
To disable monitoring for database server, use the following command fmsdisable -so -ti or fmsdisable -so -ed
To disable monitoring for filesystem fmsdisable -s ':' -m 'Disk Usage' -r '' -t major -ed
Example: fmsdisable -s 'nydbupora0-m:/local/oracle/CON3PRD/oradata2' -m 'Disk Usage' -r '/local/oracle/CON3PRD/oradata2' -t critical -ed 11/01/03 fmsdisable -s 'nydbupora0-m:/local/oracle/CON3PRD/oradata2' -m 'Disk Usage' -r '/local/oracle/CON3PRD/oradata2' -t major -ed 11/01/03 For option –t -- > major should be used to disable monitoring for file system’s full between 90% & 95 %. For option –t -- > critical should be used to disable monitoring for file system’s full between 95 % & 100 %. To list the Dataservers/ filesystems that are disabled fmsdisable –list fmsdisable –list '' History of the fmssisable. fmsdisable –list -all fmsdisable –list '’ –all Script Developed for disabling file systems # Disable disk space monitoring # # Usage: disable_FS_mon.ksh servername filesystem_path end_date # # $1 : Server name # $2 : File system name # $3 : Date in mm/dd/yy # # Example: #fmsdisable -s 'nydbupora1-m:/local/oracle/LEXPRD/oradata2' -m 'Disk Usage' -r '/local/oracle/LEXPRD/oradata2' -t major
-ed 7/1/3 # echo "List of all FMS disble records" echo echo "fmsdisable -list "$1":"$2" -all" fmsdisable -list "$1":"$2" -all echo echo echo "fmsdisable -s "$1":"$2" -m 'Disk Usage' -r $2 -t major -ed "$3"" fmsdisable -s "$1":"$2" -m 'Disk Usage' -r $2 -t major -ed "$3" echo "fmsdisable -s "$1":"$2" -m 'Disk Usage' -r $2 -t critical -ed "$3"" fmsdisable -s "$1":"$2" -m 'Disk Usage' -r $2 -t critical -ed "$3" # End of Script.
2.8
Autosys Job Monitoring
aj DBA%ORA%% -- gives list of jobs for the database server along with their status. jd -- gives job description for the job. aj DBA%ORA%% | grep Where the Job Status can be: OH – On Hold - Failed jobs eventually go into this status OI – ON ICE - Running of the job has been suspended temporarily/ indefinite SU – SUCCESS AC – Active - ready to run for the day. IN – Inactive ST – Starting state For checking the Job description of the Autosys Box alone aj -L 0
2.9
Primary and off hour update for US and UK
US Primary time update is done through a script called all_servers.ksh which is under the host njoradev1:/home/dba/files/offshore_dba_srvrs_list Need to vi the file and add the userid‟s of the oncall dba‟s and run the file to get the peak time dba‟s into effect. To update the offhour oncall dba duty Need to do bupdate of the ora_dba group with the userid‟s of the offhour oncall dba with the id‟s of the 2nd level and third leval escalation included for the particular date First do a blist ora_dba offhour 9/14/04
njoradev1:/home/dba/files/offshore_dba_srvrs_list> blist ora_dba offhour Sep 14 2004 dbhakta bjoshi,dbhakta bjoshi,ghegde,dbhakta TEAM_MAN AGER Then do a bupdate with the appropriate userid‟s Bupdate ora_dba offhour 9/14/04 dbhakta bjoshi,dbhakta bjoshi,dbhakta,ghegde For Updation of offhour and Peak hour oncal Dba for UK the procedure is To login to http://bingweb and in the search string type eu_wipro_ora_dba and look for Objects Containing String "eu_wipro_ora_dba" Object Name Object Type eu_wipro_ora_dba Team
Aliases
And click the object name eu_wipro_ora_dba and look for Team schedule View, generate and update schedules. And click on View,generate and update schedules. We get the following screen where we need to pick the particular month and also whether it is night or day and choose edit and add the required oncall dba userid. Shifts Weekday Sunday Monday Monthly Schedules Year Month Shift View Edit 2003 November Day View 2003 November Night View 2003 December Day View 2003 December Night View 2004 January Day View 2004 January Night View 2004 February Day View 2004 February Night View 2004 March Day View 2004 March Night View 2004 April Day View 2004 April Night View 2004 May Day View 2004 May Night View 2004 June Day View 2004 June Night View 2004 July Day View
Tuesday - Friday Saturday
From Midnight 07:45 Midnight 07:45 19:00 midnight 07:45 19:00 midnight 07:45
- To - 07:45 - midnight - 07:45 - 19:00 - midnight - 07:45 - 19:00 - midnight - 07:45 - midnight
Shift Weekend Weekend Weekend Day Night Night Day Night Night Weekend
2004 July Night View 2004 August Day View 2004 August Night View 2004 September Day View Edit 2004 September Night View Edit
2.10
BING/ BTELL significance and reaction required
Bing is a SUPPORT-CONTACT MANAGEMENT SYSTEM (CMS): • Maintain information about objects in Lehman support teams for them. • Maintains information about object dependencies. • Sends and escalates notifications to appropriate support personnel. It is having following main functions. Send, acknowledge or list pages Send a Bing or Btell notification Acknowledge (back) escalating Bing notifications Search and display notification log More information can be find out from http://bingweb Bing: This is the paging /email system used for escalating process in Lehman , this system requires an acknowledge , this determines the contacts which are on call for a particular database system and pages them in an interval of 15 minutes , if this is not acknowledged then it goes to the next level of escalation and it ends the process when it is acknowledged. Btell: This is an other system which is used , this is an non escalating system , this determines the contacts for a particular database ,but unlike bing it only pages the first time and then quits,it does not require acknowledgement Back: Every bing (escalating notification) keeps paging and escalating to the next level until it is acknowleged , the process of acknowledging a bing is called as „ backing‟ the bing , this is done by the unix command or utility „back‟
2.11
Scripts and their awareness
All the scripts used in the US environment are kept under the particular host Example : njoradev1:/home/dba/bin
The Original scripts are under host :/home/oraclesw/utils directory and the listing as follows DBCoalesce.ksh The above script is used for coalescing the tablespaces in the Database fro releasing space after reorg ,Delete/Truncate OraExport.ksh The above script is used for doing a daily export of the database for Disaster recovery and as well as User requirements like restore and other things OraArchiveLog.ksh The Script is used for moving/purging old the archive logs in the database to release space from the arch directory and helps in cleaning up old logs and avoids Database hanging due to arch directory becoming 100% full install_gmonitor.ksh OraTempCleanup.ksh The Script is used for the cleanup of the Temp tablespace when it gets filled up due to the user initiated process . This script can be used on Dictionary managed Temp tablespaces and not on Temporary Temp tablespaces. DBSpaceReport.ksh The script is used for getting the space details of all the tablespaces on the database , this report helps in anticipating the space requirements for each of the tablespaces ,by comparing 2-3 days reports and also helps in avoiding critical breakdowns of the Production/Dev/QA databases due to space issues. DBFreeUnused.ksh The script is used for getting the Unused space which is free in the database according to the tablespace wise PruneStatsPack.ksh This script is used to purge/remove the old snapshot id‟s between particular timings/days from the database so that space can be released from the Tools tablespace DBChainedRows.ksh The script is used to get the chained rows in the database which helps in the analysis of the fragmentation problem in tablespaces DBSegInProblem.ksh The Script is used to get the space available in the tablespace and also the details about the tablespaces which has peoblem in extending next extents due to space issue .This helps in avoiding the problems arise due to contigous space not available in the tablespace
GetRowCounts.ksh This Script is used to get the row counts of some of the important tables in the Production databases on a daily/weekly basis which helps in analyzing the growth rate of the table and in turn plan the space allocation properly , so that the breakdowns due to space issue can be averted TopSQLReport_html.ksh This script is used to get all the SQL‟s which are running in the Database at a particular point of time ,This helps in analyzing the Sql‟s to find out which query is consuming more CPU time and resources, in turn slowing down the performance of the Database and the Host , and also helps to correct the same. OraBackup.ksh This Script is used take the Hot backups/Export backups of the databases, In Lehman the Oracle backups are scheduled through Autosys as per the criticality of the Database ie whether it is Production/Dev etc and the user requirement. DBAnalyze.ksh This Script is used to analyze the Database /Schema , this helps in gathering the right Statistics This is scheduled weekly/Daily depending on the user requirement DBReIndex.ksh This Script is used for Reindexing the indexes on the particular schema/schema‟s The activity can be scheduled weekly/daily depending on the user requirement. DBcontact.ksh This script is used to add user contacts of the particular schema /Database ,this helps in Communicating with the user community at the time of problem with the schema/database or informing the user community about scheduled activity on the database etc. RunPurgeLogs.ksh This Script is used to purge/remove the old archivelogs in the archive directory and also the old trace files in the cdump.bdump.udump directories.
2.12
OFA followed for US and UK Environments
The OFA followed in US Lehman environment is as follows
2.13 Oracle Server Software Configuration
Lehman uses the OFA to maintain the different versions on single platform.
Server software layout in Lehman environment is mentioned below. $ORACLE_ HOME /opt/oracle Launched /${Release} e.g. 8.15 Format/Example $ORACLE_HOME/dbs/init{ORACLE_SID}.ora -> Example: /opt/oracle/8.1.5/dbs/initCAMEO.ora --> /opt/oracle/8.1.5/admin/pfile/initCAMEO.ora Oracle Software (bin, network, rdbms etc)
admin /adump /bdump /cdump /udump /pfile /create /TAR /dba /backup (Full database dumps) /arch (Archive Logs)
/opt/oracle /local/oracle
/${Version} e.g. 8.1.6 /$ORACLE_SID
/local/oracle /local/oracle /local/oracle /local/oracle
/$ORACLE_SID /network /$ORACLE_SID /prod-->8.1.5 (Prod pts to production release of software)
/oradata[n] /oralog[n] /admin (Used if a common set of networking .ora files are used for the host.) Admin/tns_admin (For instance level network .ora files)
2.14
Global Mount Points For Oracle Software
$ORACLE_HOME /home/oraclesw/ /home/oraclient-8.1.6 /home/oraclient-8.1.7 /home/oraclient-8.1.5 /home/oraclient$version /home/oracle/prod-->
Launched It contains the all versions of RDBMS binaries and patches
network/admin/tnsnames.ora--> /home/rdbms/network/tnsnames.ora Prod points to the most recent stable production
/home/oraclient-8.1.5
release. Currently the 8.1.5 release.
/home/rdbms/network/tnsnames.ora is a global tnsnames.ora file.
2.15 Data (.dbf) File Structure
A majority of the Oracle data files reside on UNIX file systems. This offers a considerable amount of flexibility in space management and administration of the Oracle server. However, block corruption at the UNIX file system level can cause problems for all data files that reside on the corrupted file system. As a result, for large Oracle installations (Larger than 10Gig), it is recommended that RAW partitions be utilized in production. Use of RAW partitions provides a more reliable platform as well as some performance improvements. The Oracle RMAN utility can be used for conversions between raw and Unix file system implementations.
2.16 Data file, log file and control file layout standards
$ORACLE_HOME Launched Format/Example /local/oracle/$ORACLE_SID /oradata1 data01.dbf (Mount tools.dbf Point) ${ORACLE_SID}_1.ctl ${ORACLE_SID}_2.ctl /local/oracle/$ORACLE_SID /oradata2 (Mount Point)
/local/oracle/$ORACLE_SID /oralog1 (Mount Point) /local/oracle/$ORACLE_SID /oralog2 (Mount Point)
Description Data segment DBA Tools tablespace Control files 1&2 index01.dbf Index system1.dbf segment rbs01.dbf System temp01.dbf segments ${ORACLE_SID}_3.ctl Rollback ${ORACLE_SID}_4.ctl Segments Temp segments Control files 3&4 ${ORACLE_SID}1_1.rdo Redo Logs ${ORACLE_SID}2_1.rdo ${ORACLE_SID}1_2.rdo Redo Logs ${ORACLE_SID}2_2.rdo mirror
2.17
Raw data file implementation
In a raw file system deployment, the raw partition addresses are linked from the /local/oracle/ora[data/log][#] file systems. This allows an easy transition from a raw file implementation to a Unix file system implementation. In addition, migration/copy of databases from a raw device implementation to Unix file system device implementation is greatly simplified. Eg: • /local/oracle/CAMEO/oradata1/data01.db --> /dev/vx/rdsk/cameodg/oradata01 • /local/oracle/CAMEO/oradata2/index01.db --> /dev/vx/rdsk/cameodg/oraindex01 When using raw partitions, links are created from under /local/oracle/$ORACLE_SID/oradata to the actual location of the raw partition. Since the oradata/oralog directories will only contain links to the raw partition, it is not considered necessary to create four different file systems (oradata1, oradata2, oralog1 and oralog2). A single file system /local/oracle/$ORACLE_SID/oradata would suffice.
2.18 Database dumps/archive logs
The standard location for all database dumps should be /local/oracle/${ORACLE_SID}/backup. This location will contain all full/incremental database dumps. Redo logs will be archived to /local/oracle/$ORACLE_SID/arch. The standard format should be set in the init.ora file as follows: • Log_archive_format= "${ORACLE_SID}.log.%T.%S" # $ORACLE_SID is to be replaced by the instance name. • Log_archive_dest="/local/oracle/${ORACLE_SID}/arch" By default, all Oracle databases should be run in archive log mode.
2.19
When & How to Raise an LL Incident
2.20
Escalation Procedure and Outage handling
1. 15 minutes is the response back time which the paging /email system BING allows, suppose there is no response for the first 15 minutes then it again pages the primary contact for the day.still if there is no response then it goes to the third and fourth level of escalation process
All production priority calls get escalated if it violates the SLA. Response time of the Bing messages is 30 Minutes. DBA needs to response in 15 minutes.
As per current SLA if primary responsible DBA does not acknowledge the call within 15 minutes. One more Bing message will be sent after initial 15 minutes. If it is not acknowledged in 30 minutes of duration then it will be escalated to manager e.g. Steve and after next 15 minutes it gets escalated to Jake.
3
Oracle Binary Installation Procedure
3.1 Installation procedure for 8i
3.2
Installation procedure for 9i
Oracle Installations can be done in two ways
3.2.1.1 Interactive or Non-Interactive mode Installs.
For e.g.: Interactive Installation would mean installing Oracle Server software via OUI(Oracle Universal Installer). This is a GUI screen (Java Console) and the user’s/DBA’s inputs or manual intervention is required to run through the process. DISPLAY variable needs to be set which should point to :0.0. Local host means the host from where the Installation is being performed via a emulator like Exceed, Mix, X-windows etc.. The type of emulator used will depend on what product the customer is using bound by license issues. Non Interactive Installation or Silent mode install means, that no user inputs are required and instead of performing the Installation via the GUI screen, it’s done via the command line as follows:
How to perform a silent Installation requiring no user intervention. 1. Copy the Response file template provided in the response directory where you unpacked the patch set or the tar file. Step 1: In Lehman’s Environment, one should do the following: Cd /home/oraclesw/server/$VERSION Where VERSION can be 8.1.7 or 9.0.1 or 9.2.0.1 and so on. ls *.rsp The above command should list you some files. Say for eg: lehman_server.rsp.
There are a couple of other files with the extension rsp. A copy of the file is attached for reference
2. Edit the values for all fields labeled as ―‖, according to the comments and examples in the templates. In Lehman’s environment, this step could be skipped AS OF NOW, because the response file attached above has the values, precoded. However, PLEASE DO NOT use this RSP FILE FOR INSTALLING ALL VERSIONS OF ORACLE IN ALL SERVERS. THIS IS ONLY A GUIDELINE FOR UNDERSTANDING AS IS. The value for the parameter Oracle Home should be appropriately changed. Check for the following prior to starting the Installer OraInventory for the new Oracle Server Install should be placed under ORACLE_HOME. Please rename or move the previous copy of the oraInst.loc file under/var/opt/oracle , else the pointer to the ora Inventory location will be picked up from here. Take a copy of the previous oratab file. 3. Start the Installer , (after having set the DISPLAY variable-must), passing as the last argument the full path of the response file template you have edited locally with your own value of ORACLE_HOME and FROM_LOCATION of the correct products.jar file. In Lehman’s environment, you would do the following: For Eg: To install 64Bit Oracle on 64Bit, Sun OS, you shall do the following: Cd /home/oraclesw/server/9.2.0.1/64Bit/Disk1 ls *.csh Should list you some csh files say for eg: SilentInstall.csh. Sample file is attached herewith for reference.
The above file has a reference to the response file and hence the installation would proceed in a non-interactive mode. Do the following for DB Installs: Pre-Install checks:
Is oraInst.loc file already existing in /var/opt/oracle. If yes, rename the file to .old or as found appropriate. Do you have write permissions in the ORACLE_HOME directory as user oracle. If not resolve this fine. Say for 8.1.7 base install, cd /home/oraclesw/server/8.1.7/64Bit/Disk1 cp SilentInstall.csh $ORACLE_HOME/install cp lehman_server.rsp $ORACLE_HOME/install edit SilentInstall.csh to reflect the following: DISPLAY =:0.0 Change path for rsp to reflect you local rsp file. Include path for the local oraInst.loc file, so that parallel installs can be done at the same time. Make sure oraInst.loc file exists in the location mentioned. -invPtrLoc /opt/oracle/8.1.7.3/oraInst.loc else the default location would be /var/opt/oracle. If the same does not exist, then the default location for OraInventory is taken within $ORACLE_HOME. OraInst.loc file should be pre-created and a sample of the contents is given here for reference. Edit lehman_server.rsp to reflect the correct ORACLE_HOME and PROD_HOME as applicable. SHOW_ROOT_SH_CONFIRMATION, if set to false, will not prompt the user to run root.sh as root. However, this is required at the end of every fresh install and needs to be run by the root user.
More on Silent Installs: If the Unix machine on which the Oracle Server Software to be installed is fresh
(meaning, this server has not hosted any Oracle server Software , prior to this installation), then SA's support would be required to run a particular script as root. This point can also be included as part of the work plan in the remedy ticket. For development support, the business activities are expected to happen between 9a.m to 6 p.m beyond which the support would be treated as off-hour support primarily meant for production activities. This particular script root.sh should be run with root privileges and could be run ONLY once for a particular version of Oracle Server Installation. For the subsequent installs, this step is not required and in Lehman, all Server Installs are performed in the Silent mode and the parameter SHOW_ROOTSH_CONFIRMATION , is set to false, and hence will not prompt the user to run root.sh. This method of running via Silent Installs can be followed for subsequent Installs, where root.sh need not be run again.
Explanation on the parameter SHOW_ROOTSH_CONFIRMATION. #----------------------------------------------------------------------------# Name : SHOW_ROOTSH_CONFIRMATION # Datatype : Boolean # Description : Set to true to display the dialog which prompts the # user to run root.sh before a database is created. # If this is set to false, the root.sh script can be # run manually after installation, but the Database # Configuration Assistant may not have permissions to # update the oratab file if this is the first Oracle # installation on the machine. # Valid values : true, false # Default value : true # Mandatory : No #----------------------------------------------------------------------------SHOW_ROOTSH_CONFIRMATION=false Sample rsp file is attached, which is used for Installation of 64 Bit, Oracle release 9.2.0.1, server install is attached. For more information, one can refer to the directory /home/oraclesw, which hosts the Oracle Server software in a centralized location has one rsp for each version of 9i release under the directory. /home/oraclesw/server/9.2.0.1/64Bit/Disk1
Install the Server Software using Silent install shell script and Lehman server response file.
Create the DB based on requirements manually, since it’s important to follow the Lehman specific environment standards. Prior to the manual creation of DB after the Oracle Software Installation, please go through the following to understand Lehman's environment.
3.2.2 Lehman's environment specific details w.r.t Oracle Installs
Oracle Server Details other than DBU hosts: Oracle_Home will be in /opt/oracle/$version where version refers to 8.1.7.4 or 9.2.0.2 etc.. Oratab and OraInventory pointer location. /var/opt/oracle Init and Passwd files. For example cd $ORACLE_HOME/dbs lrwxrwxrwx 1 oracle dba 41 Mar 3 19:49 initGTT.ora -> /local/oracle/GTT/admin/pfile/initGTT.ora lrwxrwxrwx 1 oracle dba 38 Mar 3 19:49 orapwGTT -> /local/oracle/ GTT/admin/pfile/orapwGTT This would mean that the init and the password file for a particular instance would point to the respective files in the pfile directory of /local/oracle/$SID/admin/pfile. Sample details: ORACLE_SID------ For e.g.: GTT ORACLE_HOME----- For eg:/opt/oracle/8.1.7.4 TNS_ADMIN------- For admin:/local/oracle/GTT/admin/listener/network/admin ORACLE_ADMIN---- For eg:/local/oracle/GTT/admin Mount Point----- For eg:/local/oracle/GTT Disk Group ----- For eg: njoraprd_datadg Specific Example and Inference: In the production server njoradev1, there are 12 databases running put together, out of which 7 are running on 8.1.7, 2 on 8.1.7.4 and 3 on 9.2.0.2 Oracle versions.
Since different versions of DBs are running on the same server, some of the client requests are routed through a common listener matching the Oracle Server Version. However in some places, client requests are routed through a centralized listener. The attached zip file should tell us how many listeners are running on this machine and how many DBs are available with Oracle Home information. This is the scenario in the current design. In the proposed production setup, that will have VCS with SRDF implemented for fail over, irrespective of the number of DBs and the versions, each and every DB is expected to have a dedicated listener to process it’s client requests to minimize down time / ease maintenance / response times. Environment setting for the Oracle User Account: Following environment files are sourced via .cshrc #/var/opt/oracle/oratab # Setup the environment for the new oracle account by configuring .cshrc and .alias files. A sample of the same is attached herewith for reference. Details on Distribution/Standard locations of DB files: To understand how the files are distributed in accordance with OFA and what's followed in Lehman, refer to Oracle on Unix Lehman's standards document in dbaweb site. Location for Datafiles, Logfiles , Controlfiles and Archive logs: /local/oracle/$SID/oradata1 /local/oracle/$SID/oradata2 /local/oracle/$SID/oralog1 /local/oracle/$SID/oralog2 /local/oracle/$SID/arch Local Administrative directory specific to the instance. /local/oracle/SID/admin Directory where archive logs are stored. /local/oracle/SID/arch Directory where Rman level 0 backup dumps are located.
/local/oracle/SID/backup Home Directory of Oracle /home/oracle Mapping--opt/oracle --->Maps to /home/oracle Mapping-/opt/oracle/dba-->Maps to /home/dba TNS_ADMIN will point to /local/oracle/$SID/admin/listener Listener files: cd /local/oracle/$SID mkdir admin backup arch oradata1 oradata2 oralog1 oralog2 cd backup mkdir exp save DATA cd /local/oracle/$SID/admin mkdir bdump udump cdump create pfile listener cd listener mkdir network cd network Link info: create a link known as "admin" to point as follows admin -> /opt/oracle/8.1.7/network/admin In the existing systems, in some machines if there is more than one instance running from the same Oracle Home, then a common listener is used to route all relevant client requests based on configuration. This statement should explain the reason for the admin links as mentioned above. Please note that the setup need not be the same in ALL HOSTS and all DBs. However, in the proposed production DBU setup, that would operate on 2+1 VCS Cluster , each and every DB is expected to be built with a dedicated listener. Oracle Administration and Maintenance scripts: /home/dba/bin /home/dba/scripts Some folders and links that needs to be created for DB Admin, trace and other instance specific purposes. Create folders
cd /opt/oracle mkdir dba Client Connectivity file- tnsnames.ora Tnsnames.ora Global tnsnames.ora is located at /home/rdbms/network. On the test box the following configuration was done:
Background:
On the host njdbaprod2, two DB WIPDEV1 and WIPDEV2 are running. Both are sourced from the same Oracle Home, /opt/oracle/9.2.0.2, which is of the version 9.2.01. On upgrading the Oracle home with patchset 9.2.0.2, the binaries will reflect the upgraded version 9.2.0.2. Cd /local/oracle/wipdev1/admin/listener ln –s /opt/oracle/9.2.0.2/network/admin/listener.ora listener.ora ln –s /opt/oracle/9.2.0.2/network/admin/tnsnames.ora tnsnames.ora cd $TNS_ADMIN. Modify /Add the listener entries in the listener.ora file to listen to both the DBs. Include the client connectivity details for the two DBs in the tnsnames.ora file. In this scenario, a common listener is used to listen to both the DBs and hence the links are created as mentioned above for the listener.ora file. Also a common tnsnames.ora file is maintained to gain a better control of the DBs running on that particular host. Please specify host aliases in place of host in both listener and tnsnames.ora. For eg: in the following example, the host name WIPDEV2 and WIPDEV1 SHOULD BE REPLACED by the host aliases wipdev2host and wipdev1host. The client connectivity details for all the DBs are configured in one global tnsnames.ora file. Sample entry: WIPDEV1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = wipdev2host)(PORT = 1521)) ) (CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = WIPDEV1) ) )
3.3
Installation Procedure on the Hosts across the Firewall
4
Database Creation
4.1 Database Creation on 8.1.7
4.2
Database Creation on 9i
4.3
Database Creation with different Block size and Character Set
5
Lehmanising the new Oracle Databases
5.1 Setting up the Database Monitoring
Lehman Operational Process
5.2
Database Monitoring
Gryphon, HP-OV and many shell scripts are monitoring databases. Gryphon and HP-OV are described below. Scripts are described in next section. Configuring the Gryphon and HP-OV are described in detail in the database administration chapter.
5.3 Gryphon
Gryphon is a distributed system-monitoring platform that detects and reports faults. It comprises a set of system, db-server, application and cross monitors run by a centralized management facility. Exceptions are passed to the Fault Management System (FMS) that decides which actions should be taken and executes them. Details of the Gryphon can be find out from http://autoweb:8080
Various monitors for Sybase, Oracle, MS-SQL server, Informix, DB2 including: Server Availability monitors Database Connections Blocked Process Monitor Replication Server Health Monitor Database Replication Latency (Oracle, MS-SQL server, Informix, DB2) Data server Status (check whether loading, recoverable, offline etc.). Data server errorlog /syslog LogReader
Gryphon is being slowly being migrated to HP-OV. HP-OV has been described below.
5.4 HP-OV
HP-OV Oracle DB SPI is configured for Oracle database monitoring. It is going to replace Gryphon. Oracle metrics defined in HP_OV are mentioned in Appendix B. HP-OV matrix. HP-OV Console From a performance monitoring point of view, Stats pack is widely used and the various metrics configured in HP-OV will prove to be a great source of information. Currently HP-OV was configured for all development Oracle Dbs and is would be rolled out in production shortly once testing is complete. Following URL takes us to one of the currently configured HP-OV web interface page. This http://njgisrpt002/HPOV_Reports/OVPM.htm Generic information on HP-OV: http://www.openview.hp.com/products/spi/spi_oracle/index.html>
5.4 Setting up the Database Backup
The Backup of all the Databases on Lehman environment is configured through Autosys and taken using RMAN backup tool
A brief overview and the usage of the Backup Script which is Stored under /home/dba/bin OracleBackup.ksh # This script will back up the Oracle database to disk # Functions performed are: # 1) Using RMAN, perform a full/incremental dump # 2) Back up the control file to trace and dump file system # 3) Get the schema of the database # 4) Generate log switches
# # # # # #
5) 6) 7) 8) 9) 10)
Update DBACENTRAL with success of job. Eliminate the LogAfterDump pages. Set SYBASE variable correctly Option to export databases that have noarchivelog set Option to perform incremental database Rman backups. Modify hp_permissions
**************** Oracle database backup utility Usage************* In order to use the OraBackup.ksh utility to backup an oracle database you need to determine if you need to take hot backups or database exports. In order to take hot backups using Rman, the database has to be in archivelog mode. Exports can be taken with the database in archive log mode or not. Hot Backups =========== 1) Create a config file under /home/dba/files/help.NP.cfg 2) Add any configuration parameters that need to be modified here 3) If a config file is not created, database will still be dumped using defaults 4) Backups are dumped into /local/oracle/help/backup by default 5) Make sure that there is a LogAfterDump directory in the arch destination 6) Usage is OraBackup.ksh Export Backups ============== 1) Create a config file under /home/dba/files/help.NP.cfg 2) Add/modify any configuration parameters here to override defaults 3) If a config file is not created, database will still be exported using defaults 4) Backups are dumped into /local/oracle/help/backup/exp by default 5) Usage is OraBackup.ksh TEST_DB /home/dba/files/TEST_DB.exp.cfg export or OraBackup.ksh export OraBackup.ksh Version 5.1.4 usage: OraBackup.ksh |[server_name.cfg]| server name: name of oracle instance cfg : cfg file for Rman dump or export
================================================
5.4.1 More on OraBackup.ksh
When run with the following input, what does it do? For example, OraBackup.ksh WIPDEV1 This script will try to find for the configuration file for the DB entered as the parameter. If the same is not available then it goes by the default values. Next it makes use of the utility getsap to find the password for the DB. If the DataCompass is not configured for this particular DB, then the password will not be available to the script and hence will throw the following message: Can’t find password for server Then it tries to open the crontab file. If the crontab file, doesn’t exist then it would give the following error. Crontab: can’t open your crontab file. If the DB is not in archive log mode, then the RMAN online/hot backup will not go through and the following error could be encountered. ORA-19602 cannot backup or copy active file in NOARCHIVELOG mode, while running the OraBackup.ksh script as OraBackup.ksh WIPDEV1. Also the script will btell/page the corresponding users for the errors encountered when the OraBackup.ksh script is run. In order that the errors be communicated via whatever may be the mode, the utility btell, bing etc should be accessible. That if you issue which btell/bing, the corresponding path should be displayed. Else the same needs to be configured. Normally it is found in the following directory. /opt/bin which (maps to /usr/local/bin) Prior to scheduling the backup via Autosys, please run OraBackup.ksh at least once manually on the system, to check if it’s working fine. Please take care to see all the values set for the parameters defined in the backup configuration file for the DB are valid and existing. The configuration file is kept under /home/dba/files Naming convention is .NP.cfg
5.5
Setting up the Performance Statistics
5.6
Scripts & Jobs to be set for the new Database Environment.
5.7
In perspective of UK Environment
5.8
Setting up the Database Password using getsap/putsap
5.9
Configuring DataCompass for a new DB:
Once a new DB is created, administration information pertaining to the new server should be configured in Data Compass. How to include a new cluster and DB related information via DataCompass:
Steps to be followed , in order to include the entries pertaining to a new server:
First of all privileges are required to be granted to work with DataCompass functions. Lehman Manager(Steve) had raised a request to the automation group asking for the same. Userids bgovindh, dbhakta and (Rajeev Srivastava) in the Oracle team have the privileges to update DataCompass.
How to configure DataCompass for a new server(DB)?
Launch DataCompass- Log into bo-xsrv as the privileged Oracle user. cd /home/dbapps/DC To launch DataCompass, logging though an emulator is essential. One can use Exceed or any licensed emulator as authorized bound by license issues.
Having launched DataCompass, click on Expand button seen on the second half of the screen. Only if the userid has the necessary privileges, expand button will expand to the following and two more new rows will be visible, else the following message will appear. Please email the automation group for details.
In order to include the details for a new server, a new cluster should be created. To create a new cluster, key in or select one of the existing values for the following parameters, having consulted your supervisor. Cluster, server, machine, role and Business Line. For eg: to add a new cluster named DBA_TEST, key in the server name, vendor (optional), machine name, role, business line and host name. Sample values are given as follows: Cluster name : DBA_TEST Server: WIPDEV1 Vendor: Oracle Role: Select Develop. Host:njdbaprod2 Business Line: FI—Consult this with your supervisor. Having keyed in or selected one of the above, you need to click on add cluster. So now DataCompass is configured for the new DB. If you move your mouse pointer on the respective buttons in the Data Compass, the details of the parameters required are displayed at the bottom of the Data Compass screen.
Remove cluster or server: If the entries in DataCompass pertaining to a cluster or server has to be removed. First Think twice. Second consult your supervisor. Third, you can get the password from your supervisor and then delete the entries. Add/modify Data compass
Change sa password.
Once the entries are added using datacompass, Check it out using the following commands. WIPDEV1:/opt/bin> HostForServer WIPDEV1 Njdbaprod2 WIPDEV1:/opt/bin> ServerOnHost njdbaprod2 WIPDEV1:/opt/bin> RoleForServer WIPDEV1 develop NOTE: The cluster name and Server name should be unique in Datacompass. Install sa password for getsap/putsap
To add the password issue the following.
/opt/sybase/dba/bin/putsap NJDBUDSYB1 putsap is available only in Sybase servers. If the same has to be made available for Oracle Servers then the interface file has to be configured based on the host alias for the DB.— How to configure the interface file and update the password for the new Oracle DBs in the DBACENTRAL DB? Why is this required.?
putsap is designed to interact with Sybase servers primarily. This means when putsap command is issued with some parameters, it considers the target server/entity/object to be a Sybase server.
This being the case, even if you log into a Sybase server and issue the command putsap WIPDEV1 , it will throw the following error saying, can’t connect to the server WIPDEV1.The reason being, WIPDEV1 is not a Sybase server and it’s an oracle server. So, if we can make putsap understand that WIPDEV1 is a Sybase server , then we can get putsap working for us to update the sa password for the new Oracle DBs in DBACENTRAL DB.
Connection to a Sybase server happens via the interface file. Interface file is similar to tnsnames.ora file. Global tnsnames.ora or interfaces file is owned by root and while making changes to these global files, we ought to be very careful as the changes gets reflected globally across instances. So how to do this? Mandatory points to Note: DBACENTRAL is a Live DB. Should be handled cautiously and carefully. Initially, if Oracle DBAs are not comfortable updating SA password for new oracle DBs, they SHOULD approach Wipro Sybase team to get problems resolved and not to hamper DBACENTRAL DB in any way. However the following procedure is documented as part of Knowledge gathering phase: Log into a Sybase server, where interfaces file is not referring to the global interfaces file. How to find out? Configuration in the Sybase interfaces file: Log into Sybase host cd ls –ltr interfaces. Make sure to check , interfaces file is not a global one. Cp interfaces interfaces.orig Vi interfaces search for the entry DBACENTRAL2 and copy it to the end of the file. Change the word DBACENTRAL2 to the new Oracle DBName, for which you are trying to update sa password into the DBACENTRAL DB. For eg: say the new Oracle DB name is WIPDEV1, now DBACENTRAL string is changed to WIPDEV1 in the copied lines in the interface file.
Please note: YOU ARE GOING TO COPY DBACENTRAL2 entries in the interface file to the end and proceed further and NOT DBACENTRAL entry. To repeat! Please note the difference.
Getsap DBACENTRAL2 Note the password for the DBACENTRAL2 DB. Say the passwd is XX2 Loading password for the Oracle Instance WIPDEV1 Let’s assume that the system passwd for the new Oracle DB WIPDEV1 is Oraxxx
Now load the password for the WIPDEV1 DB into the DBACENTRAL2 DB. Now we are logging into the DBACENTRAL2 DB, to load the password for the Oracle DB WIPDEV1. isql -Usa –S DBACENTRAL2 –P XX2 sp_password XX2, Oraxxx go exit Update the password for WIPDEV1 via putsap. putsap WIPDEV1 Oraxxx Reset the password for DBACENTRAL2 as before. This is essential and IMPORTANT because, previously we logged into the DBACENTRAL2 DB for WIPDEV1 having personified DBACENTRAL2. It is important to reset the password for DBACNETRAL for the help (DBACENTRAL2) has done in personifying WIPDEV1 as a Sybase Server. isql –Usa –S DBACENTRAL2 –P Oraxxx sp_password Oraxxx, XX2 go exit Reset entries in interfaces file: vi /opt/Sybase/interfaces---(if this is the correct path) then remove the entries that was included in the beginning of this exercise for the new Oracle DB. WIPDEV1. Confirmation Getsap WIPDEV1 Check this is the password you want for sa of WIPDEV1. Getsap DBACENTRAL2 Check to see you are seeing the same password as the one, you saw at the beginning of this exercise.
DataCompass -Server Configuration Data Validation: We can make use of the following DataCompass functions to understand if the data in datacompass is configured correctly. HostForServer ServerOnHost ShowServerInfo
ShowRoles ShowBL
1
njtrainprod1:/usr/local/sybase/ASE-12_5> isql -Usa -Plegion -SETGSYBDEV1 1> sp_password legion,ecomm1 2> go Password correctly set. (return status = 0) 1> exit njtrainprod1:/usr/local/sybase/ASE-12_5> putsap LONCTPRD ecomm1 ecomm1 njtrainprod1:/usr/local/sybase/ASE-12_5> getsap LONCTPRD ecomm1 njtrainprod1:/usr/local/sybase/ASE-12_5> isql -Usa -Plegion -SETGSYBDEV1 Msg 4002, Level 14, State 1: Server 'ETGSYBDEV1', Line 1: Login failed. CT-LIBRARY error: ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed. njtrainprod1:/usr/local/sybase/ASE-12_5> isql -Usa -Pecomm1 -SETGSYBDEV1 sp_password ecomm1,legion go Password correctly set. (return status = 0) 1> exit njtrainprod1:/usr/local/sybase/ASE-12_5> getsap ETGSYBDEV1 legion njtrainprod1:/usr/local/sybase/ASE-12_5>
2
3
4
5
5.10
Updating the Database Contacts (ADB/dbcontact/banner)
Procedure to update database contact information tips.primaryserverinfo SQL>delete primaryserver_info where dataservername = 'NYESERV' and databasename = 'all_schemas' SQL>select * from primaryserver_info where dataservername like '%TOMS%' pdate primaryserver_info set primary_contact = 'anadgir,bob_dba', mail_id = 'bob_dba' where dataservername like '%INFRAPROD_RS%'
insert primaryserver_info values ("NJCMTOMS", "all_schemas", "sgilliga,raptor", "akalyans", "raptor", "Outlookgroup RAPTOR") To add a new entry SQL>insert primaryserver_info (dataservername, databasename, primary_contact, secondary_contact, mail_id, instruction) values ("NJSMCTDV", "ECON", "ssardana", "jfricchi", "ssardana,jfricchi","eContributor") To query SQL>select * from primaryserver_info where databasename = 'ECON'
6
Post DB Creation Tasks
6.1 Installing Oracle 9i JVM
The following is a detailed list of scripts that need to be executed to install the JVM successfully in Oracle9i. SCOPE & APPLICATION For DBA's and Java Developers. NOTE Before installing the JVM manually, please refer to Note:204935.1 for a script that will automatically check the system requirements of your database and inform you if it does not meet them. Also refer to Note:202914.1 on how to cleanup the JVM in 9.0.1 as this is necessary prior to manually re-installing the JVM. An incomplete or failed attempt to install JVM requires a complete cleanup before a fresh install of JVM manually. All JVM related scripts must be run under the SYS AS SYSDBA account. Installing JVM manually Using the database configuration assistant, the following scripts will be executed: Setup a database for running Java and the ORB SQL>@$ORACLE_HOME/javavm/install/initjvm.sql; INITialize (load) XML components in JServer SQL>@$ORACLE_HOME/xdk/admin/initxml.sql;
Loads NCOMP'ed XML Parser SQL>@$ORACLE_HOME/xdk/admin/xmlja.sql; loads the XMLSQL Utility (XSU) into the database. SQL>@$ORACLE_HOME/rdbms/admin/catxsu.sql; nstall the Oracle Servlet Engine (OSE) SQL>@$ORACLE_HOME/javavm/install/init_jis.sql ; dds the set of default end points to the server -- with hardcoded values for the admin service SQL>@$ORACLE_HOME/javavm/install/jisaephc.sql ; turn on J Accelerator SQL>@$ORACLE_HOME/javavm/install/jisja.sql ; register EJB/Corba Dynamic Registration Endpoint SQL>@$ORACLE_HOME/javavm/install/jisdr.sql 2481 2482; init Java server pages ??? SQL>@$ORACLE_HOME/jsp/install/initjsp.sql; turn on J Accelerator for JSP libs SQL>@$ORACLE_HOME/jsp/install/jspja.sql; script used to load AQ/JMS jar files into the database SQL>@$ORACLE_HOME/rdbms/admin/initjms.sql; load RepAPI server classes and publish 'repapi' obj SQL>@$ORACLE_HOME/rdbms/admin/initrapi.sql; loads sql, objects, extensibility and xml related java SQL>@$ORACLE_HOME/rdbms/admin/initsoxx.sql; Loads appctxapi.jar for JavaVm enabled Database.Called by jcoreini.tsc SQL>@$ORACLE_HOME/rdbms/admin/initapcx.sql; script used to load CDC jar files into the database SQL>@$ORACLE_HOME/rdbms/admin/initcdc.sql; Loads the Java stored procedures as required by the -- Summary Advisor. SQL>@$ORACLE_HOME/rdbms/admin/initqsma.sql; Initialize sqlj type feature in 9i db SQL>@$ORACLE_HOME/rdbms/admin/initsjty.sql; load java componenets for AQ HTTP Propagation SQL>@$ORACLE_HOME/rdbms/admin/initaqhp.sql;
NOTE: Running jisdr.sql causes problems on OpenVMS. Please see
Note:197506.1 listener dies shortly after startup after running jisdr.sql Please follow ora_root:[javavm.doc]readmevms.doc to install javavm on OpenVMS. RELATED DOCUMENTS NOTE.132936.1 What scripts are needed to manually install Oracle8i JVM Note.105472.1 Setup, Configuration, and Use of the Java Virtual Machine (JVM) Note.103855.1 Jserver Installation for Oracle8i Note:204935.1 How to check the system requirements for a JVM installation Note:202914.1 How to cleanup the JVM in 9.0.1
-
TNS
6.2
Install XDK kit in ORACLE_SID PROD. Preparatory Phase I 1 2 3 4 5 6 7 8 9 10
How to install JVM(XDK) on 9i DB
/local/oracle/ORACLE_SID/> mkdir create rsh nyecomdb3-m:/local/oracle/NYESERV/create rcp -rp JVM hostname:/local/oracle/ORACLE_SID/create & hostname:/local/oracle/ORACLE_SID/admin/pfile cp initORACLE_SID.ora initORACLE_SID.ora.20May04 Check free space in System file and it should be a minimum of 150 MB atleast.Taken care in dependency, space has been added already. Check and make a note of all invalid objects. Run utlrp.sql once before starting the install. @$ORACLE_HOME/rdbms/admin/utlrp.sql Communiate to the users that ORACLE_SID DB will not be available starting 10:A.M on Satuday morning until XDK work is complete. Btell ahukmani,ora_dba,ora_dba,ORACLE_SID,ldmello "FYI-->ORACLE_SID DB will not be available 10:A.M on Satuday morning until XDK work is complete". Save changes to the DB. alter system checkpoint; alter system archive log current; alter system archive log current; alter system archive log current; alter system switch logfile; alter system switch logfile;
alter system switch logifle; Preparatory Phase II: Run the following commands and save the output. Please DO NOT FORGET to spool the output for all the scripts. Svrmgrl Connect / as sysdba 1 2 3 select name from v$database; select count(1), owner, object_type, object_name from dba_objects where object_name like 'JAVA% group by owner, object_type, object_name Make a note of the output. This is important. SELECT dbms_java.longname(name) FROM sys.obj$ WHERE type# = 29 and status != 1; It's OK , if you get the following error; "dbms_java package does not exist" cd hostname:/local/oracle/ORACLE_SID/create/JVM/dbascripts SQL> connect / as sysdba SQL> @initjvmauxsupport.sql alter system checkpoint; alter system archive log current; alter system archive log current; alter system checkpoint; alter system switch logfile; alter system switch logfile; alter system archive log current; SQL> @@check_java_system.sql SELECT * FROM V$SGASTAT WHERE pool = 'java pool'; Make a note of the output. @/home/dba/sql/freespace.sql @$ORACLE_HOME/rdbms/admin/utlrp.sql select count(1) , owner, object_type from dba_objects where status!='VALID' group by owner, object_type; select count(1) , owner, object_name, object_type from dba_objects where status!='VALID' group by owner, object_type, object_name; It's OK if you see the following objects as invalid. COUNT(1) OWNER OBJECT_NAME OBJECT_TYPE ---------- ---------- -------------------- -----------------1 SYS DBMS_ASYNCRPC_PUSH PACKAGE BODY 1 SYS DBMS_DEFER PACKAGE BODY 1 SYS DBMS_DEFER_SYS_PART1 PACKAGE BODY 1 SYS DBMS_REPCAT_MAS PACKAGE BODY 1 SYS DBMS_REPCAT_UTL3 PACKAGE BODY 1 SYS DOMSAMPLE PROCEDURE 1 SYS XSLSAMPLE PROCEDURE 1 SYS SPACER_ALLOC VIEW 1 SYS SPACER_BYTE VIEW 1 SYS SPACER_OVERALL VIEW 1 SYS SPACER_TS_STAT VIEW
4
5 6 7 8 9
10 11 12 13 14
15 16
17
18 19
10 rows selected. sho user USER is "SYS"------->Please note that it should be SYS. select name from v$database; drop procedure DOMSAMPLE; Procedure dropped. drop procedure XSLSAMPLE; Procedure dropped. select obj#, name from obj$ where type#=28 or type#=29 or type#=30 or namespace=32; Should return the following: no rows selected select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='SYSTEM'; Please make sure you have atleast 150 to 200 MB free. Svrmgrl connect internal SVRMGR> select object_name, object_type, owner from all_objects where owner = 'SYS' and status 'VALID'; @$ORACLE_HOME/rdbms/admin/utlrp.sql SVRMGR> alter system checkpoint; Statement processed. SVRMGR> alter system archive log current; Statement processed. select server,substr(username,1,15) "ORACLE USERNAME", substr(osuser,1,8) "O/S USER", substr(machine,1,7) "MACHINE", substr(program,1,35) "PROGRAM" from v$session where type='USER'; You should not see any active USER connections in here. select count(1), object_type, owner from dba_objects group by object_type, owner; select object_name from user_objects where object_type = 'JAVA CLASS' and status = 'INVALID'; Should return the following: OBJECT_NAME ------------------------------------------------------------------------------0 rows selected. Shrink all rollback segments before executing any scripts. Execute the output of the following; select ' alter rollback segment ' || segment_name || ' shrink; ' from dba_rollback_segs; Save changes to the DB. Stop ORACLE_SID Primary Listener. Changes to init for JVM Install and Otrace turn off. cp initORACLE_SID.ora initORACLE_SID.ora.date. Shutdown the Primary Database (ORACLE_SID) , edit the init.ora file and set the parameters #userid. Date parameters are set for JVM install. _SYSTEM_TRIG_ENABLED = FALSE java_pool_size=36700160 #userid.Date parameters set for otrace turn off oracle_trace_enable=FALSE Rename the following *.dat to *.dat.hold hostname:/opt/oracle/8.1.7/otrace/admin/*.dat (to speed up sqlplus)
20
21 22
Startup mount Alter database archivelog; Alter database open; ORACLE_SID in in no archivelog mode now. 23 Now DB should be up and running in no archivelog mode and with NO LISTENER. Run JVM Install Scripts. Required scripts are available at hostname:/local/oracle/ORACLE_SID/create/JVM/dbascripts. connect using svrmgrl and as INTERNAL in 8i This is IMPORTANT AND MANDATORY. STOP , CHECK , REVIEW the logs after running each script. YOU SHOULD RUN THE SCRIPTS AS INTERNAL OR SYS AS SYSDBA ONLY. DO FOLLOW THE GUIDELINE. If you are not sure at any point, call me @ 848-203-6893. Run the following scripts manually via SVRMGRL ONLY and Only as INTERNAL. Please NOTE . This is IMPORTANT and needs to be followed. In 8i cd /local/oracle/ORACLE_SID/create/JVM/dbascripts @run_initjvm.sql @run_initxml.sql @run_catxsu.sql @run_init_jis.sql @run_jis_ja.sql @run_jisaephc.sql @run_initplgs.sql @run_initjsp.sql @run_jspja.sql @run_initplsj.sql @run_initjms.sql @run_initrepapi.sql @run_initsoxx.sql in 9i Install JVM manually, Follow the steps as in section 1.1. @$ORACLE_HOME/rdbms/admin/utlrp.sql select count(1), object_type, owner from dba_objects group by object_type, owner; SVRMGR> select object_name from user_objects where object_type = 'JAVA CLASS' and status = 'INVALID'; Issue alter system checkpoint; alter system switch logfile; At this point, On issuing the following command, your count SHOULD BE THE FOLLOWING (THIS IS MANDATORY). If not call Bhavani at 848-203-6893. Please note DO NOT PROCEED ANY FURTHER, IF THE COUNT OF OBJECTS IS DEVIATING FROM STANDARDS.YOU NEED TO REVIEW YOUR SCRIPTS AND THE LOG. SQL> select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;
24
COUNT(*) OBJECT_TYPE ---------- -----------------9971 JAVA CLASS 290 JAVA DATA 132 JAVA RESOURCE 8 JAVA SOURCE It's OK if you still see the following objects as INVALID. SQL> select object_name, object_type, owner from all_objects status 'VALID'; OBJECT_NAME OBJECT_TYPE OWNER ------------------------------ ----------------------------------------------DBMS_ASYNCRPC_PUSH PACKAGE BODY SYS DBMS_DEFER PACKAGE BODY SYS DBMS_DEFER_SYS_PART1 PACKAGE BODY SYS DBMS_REPCAT_MAS PACKAGE BODY SYS DBMS_REPCAT_UTL3 PACKAGE BODY SYS SPACER_ALLOC VIEW SYS SPACER_BYTE VIEW SYS SPACER_OVERALL VIEW SYS SPACER_TS_STAT VIEW SYS 9 rows selected. select server,substr(username,1,15) xxxxxxx, logon_time ,substr(osuser,1,8) "O/S USER", substr(machine,1,7) "MACHINE", substr(program,1,35) "PROGRAM" from v$session where type='USER'; Information: Reading Preparation: READ loadjava.steps.. DO NOT EXECUTE YET. ONLY READ loadjava.steps. DO NOT EXECUTE ANYTHING IN IT YET. cd hostname:/local/oracle/ORACLE_SID/create/JVM/dbascripts. ORACLE_SID XDK Install: cd hostname:/local/oracle/ORACLE_SID/create/JVM/dbascripts Script execution: Create XML user. @createuser.sql connect sys@ORACLE_SID grant drop public synonym to xml; grant create public synonym to xml; GRANT CONNECT, RESOURCE, JAVAUSERPRIV, JAVASYSPRIV TO xml; Load XDK Java Classes into DB cd hostname:/local/oracle/ORACLE_SID/create/JVM/XML/lib loadjava -user xml/xml@ORACLE_SID -r -v xmlparserv2.jar There should be no errors for loadjava. cd hostname:/local/oracle/ORACLE_SID/backup/ORACLE_SID/create/JVM/PLSQL/lib where
loadjava -user xml/xml@ORACLE_SID -r -v xmlplsql.jar There should be no errors for loadjava. ls -ltr /home/oraclesw/server/XML/bin/../lib/xsu12_817.jar loadjava -r -v -g public -u xml/xml@ORACLE_SID -noverify -s /home/oraclesw/server/XML/bin/../lib/xsu12_817.jar If loadjava comes out with one error it is fine. But please call Bhavani @ 848-203-6893 if you get any error. Executing XML/XDK oriented scripts into DB. connect sys@ORACLE_SID grant drop public synonym to xml; grant create public synonym to xml; GRANT CONNECT, RESOURCE, JAVAUSERPRIV, JAVASYSPRIV TO xml; sqlplus xml/xml@$ORACLE_SID @$ORACLE_HOME/xdk/plsql/parser/bin/load.sql @/home/oraclesw/server/XML/xdk/admin/xmlload.sql @/home/oraclesw/server/XML/xdk/admin/xmlgen.sql Connect sys@ORACLE_SID @public_synonym.sql @grant_execute.sql (It's OK if you get the following error: GRANT EXECUTE ON LOADLOBS TO PUBLIC ORA-04042: procedure, function, package, or package body does not exist) sqlplus xml/xml@$ORACLE_SID @$ORACLE_HOME/xdk/plsql/demo/domsample.sql @$ORACLE_HOME/xdk/plsql/demo/xslsample.sql execute domsample('/home/oracle/8.1.7/xdk/plsql/demo/','family.xml','errors.txt'); execute xslsample('/home/oracle/8.1.7/xdk/plsql/demo','iden.xsl','family.xml', 'family.out','errors.txt'); CREATE OR REPLACE FUNCTION XMLVersion RETURN VARCHAR2 IS LANGUAGE JAVA NAME 'oracle.xml.parser.v2.XMLParser.getReleaseVersion() returns java.lang.String'; / select xmlversion from dual; connect sys/ORACLE_SID2sp@ORACLE_SID Connected. @$ORACLE_HOME/rdbms/admin/utlrp.sql GRANT JAVAUSERPRIV, JAVASYSPRIV TO PUBLIC GRANT EXECUTE ON LOADLOBS TO PUBLIC CREATE OR REPLACE FUNCTION XMLVersion RETURN VARCHAR2 IS LANGUAGE JAVA NAME 'oracle.xml.parser.v2.XMLParser.getReleaseVersion() returns java.lang.String'; / select xmlversion from dual; XML version should be the following:
XMLVERSION ------------------------------------------------------------------------------Oracle XDK Java 9.2.0.4.0 Production Check for grants on all Standard XML routines. Connect sys as sysdba or internal in ORACLE_SID @/local/oracle/ORACLE_SID/create/JVM/dbascripts/grants_priv_to_sys.sql @$ORACLE_HOME/rdbms/admin/utlrp.sql 1* select count(1), object_type, owner from dba_objects where object_name like 'XML%' group by object_type, owner SQL> / COUNT(1) OBJECT_TYPE OWNER ---------- ------------------ -----------------------------1 FUNCTION SYS 1 FUNCTION XML 17 PACKAGE XML 3 PACKAGE BODY XML 17 SYNONYM PUBLIC SVRMGR> sho parameter system NAME TYPE VALUE ----------------------------------- ------- -----------------------------_system_trig_enabled boolean FALSE SVRMGR> Statement SVRMGR> Statement SVRMGR> SVRMGR> SVRMGR> Statement SVRMGR> Statement SVRMGR> Statement SVRMGR> Statement alter system processed. alter system processed. / / alter system processed. alter system processed. alter system processed. alter system processed. checkpoint; switch logfile;
switch logfile; switch logfile; switch logfile; switch logfile;
Shutdown DB. Reset Install time init parameters: Cp initORACLE_SID.ora initORACLE_SID.ora.postxdk.date REMOVE _system_trig_enabled -FALSE from init. Rest of the parameters like java_Pool_size and otrace can remain as such. Startup Listener. Restart DB. Startup mount Alter database archivelog;
Alter database open; Check for client connectivity via sqlplus as perfstat/perfstat@ORACLE_SID. SVRMGR> sho parameter system Archive log list; Alter system archive log current; Alter system switch logfile; Inform sybase and DB2rep to check for replication. Please inform sybase team to startup servers in the following order. RUN_NJORACLE_SIDSYB1_DIRECT RUN_ORACLE_SID_DC RUN_NJORACLE_SIDSYBPROD RUN_NJORACLE_SIDSYBPROD_BCK RUN_NJORACLE_SIDPROD_RS Btell ora_dba,ora_dba "FYI-->ORACLE_SID is back online after XDK install.--On call dba-xtn" On confirmation from ora_dba, you can page the user group. Btell ahukmani,ora_dba,ora_dba,ORACLE_SID,ldmello "All-ORACLE_SID is back online after XDK Install. Please restart Application" Check for the status of all ORACLE_SID Primary /DR Autosys jobs. Escalation Contacts: ora_dba Hegde Rinnappa Ratanabodint
6.3
Reload the JVM in 9.2.0.X
The following section details how to completely de-install and then re-install the JVM in Oracle9i Release 2 (a.k.a 9.2.0.x), on UNIX, Windows NT/2000/XP and HP OpenVMS. SCOPE & APPLICATION The actions detailed in this article are highly destructive and so should not be attempted by novice users. Additionally, the actions performed in this article make direct changes to the underlying data dictionary tables which should not be attempted unless under the guidance of Oracle Support Services. As with all destructive actions, Oracle Corporation strongly advises taking a FULL COLD BACKUP of the database before these actions are performed. In addition, as removing the JVM removes all user owned JAVA objects also, such as APPS schema objects, an export dump of all user schemas containing JAVA objects should be taken if required, so that these objects can be reloaded once the JVM has been re-installed (see Note:183825.1). Oracle Applications customers should contact Oracle Support rather than attempt the actions in this article. NOTE: It is not necessary to install the XML Developers Kit (XDK) or the XML SQL Utility (XSU) into user schemas in 9.2.0.x, unless specifically required by your application, as these products are included in the JVM in 9.2.0.x.
How to Reload the JVM in 9.2.0.x The following steps need to be executed precisely as listed below to ensure that they complete successfully: Verify the following system requirements are available: The Shared Pool has at least 24Mb of free space. The Java Pool has at least 12Mb of free space The SYSTEM tablespace has at least 70Mb of free space The SYSTEM RBS has at least 100Mb of free space The initjvm.sql script in 9.2 will check these resources are available when it is run, and if they aren't available the execution of the script will terminate with an error indicating which resource needs to be increased. 2 Shutdown the instance and then create and run the following sql script from a new sqlplus session: -- Start of File full_rmjvm.sql spool full_rmjvm.log set echo on connect / as sysdba startup mount alter system set "_system_trig_enabled" = false scope=memory; alter system enable restricted session; alter database open; show parameters _system @?/rdbms/admin/rmaqjms.sql @?/rdbms/admin/rmcdc.sql @?/xdk/admin/rmxml.sql @?/javavm/install/rmjvm.sql truncate table java$jvm$status; select * from obj$ where obj#=0 and type#=0; delete from obj$ where obj#=0 and type#=0; commit; select owner, count(*) from all_objects where object_type like '%JAVA%' group by owner; select obj#, name from obj$ where type#=28 or type#=29 or type#=30 or namespace=32; select o1.name from obj$ o1,obj$ o2 where o1.type#=5 and o1.owner#=1 and o1.name=o2.name and o2.type#=29; shutdown immediate set echo off spool off exit End of File full_rmjvm.sql The actions above are required to resolve some known issues, such as bug:1365707. IMPORTANT NOTES a. The SHUTDOWN command is needed here to ensure that the database is synchronized to disk after running rmjvm.sql. Failure to perform this action can result in spurious errors such as: ORA-29549: class %s.%s has changed, Java session state cleared when running initjvm.sql. 1
b. The EXIT command is needed here to ensure that the sqlplus session resynchronizes its cache with the database, again to prevent spurious errors occuring when running the JVM initialiazation scripts. c. Check that the prevoius script completed ok. You can ignore object doesn't exist type errors here such as: ORA-04043: object XMLGEN does not exist ORA-01432: public synonym to be dropped does not exist are raised when running rmxml.sql. The last three select statements should show no java objects remaining, and that the SYS.JAVA$CLASS$MD5$TABLE was removed successfully before proceeding. In some cases you may see some rows returned by the last but one query, e.g: SVRMGR> select obj#, name from obj$ 2> where type#=28 or type#=29 or type#=30 or namespace=32; OBJ# NAME ---------- -----------------------------44909 JAVA$POLICY$SHARED$00000001 44910 JAVA$POLICY$SHARED$00000002 2 rows selected. These can be safely ignored if they only reference object names of the form 'JAVA$POLICY$SHARED$', providing they no longer appear once the instance is restarted. If any other rows are returned, or if you have any doubts, stop at this point and contact Oracle Support supplying the log file generated above. If the above commands completed successfully, then create and run the following sql script: -- Start of File full_jvminst.sql spool full_jvminst.log; set echo on connect / as sysdba startup mount alter system set "_system_trig_enabled" = false scope=memory; alter database open; show parameters _system select obj#, name from obj$ where type#=28 or type#=29 or type#=30 or namespace=32; @?/javavm/install/initjvm.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @?/xdk/admin/initxml.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @?/xdk/admin/xmlja.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @?/rdbms/admin/catjava.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; shutdown immediate set echo off spool off
exit End of File full_jvminst.sql d. Once the database has been restarted, resolve any invalid objects by running the utlrp.sql script e.g.: @?/rdbms/admin/utlrp.sql
The JVM should now be fully installed (including the XDK & XSU). The count of Java objects returned above should be around 9,260 (on NT) for the SYS user. In a full JVM installation depending upon the platform and products installed, the objects can be broken down by owner as follows: SQL> select count(*), owner from all_objects where object_type like '%JAVA%' group by owner; COUNT(*) OWNER ---------- -----------------------------247 ODM 1 ORDPLUGINS 903 ORDSYS 9261 SYS 40 WKSYS If you previously had the InterMedia (ORDSYS & ORDPLUGINS), UltraSearch (WKSYS) or Data Mining (ODM) options installed, then these will need to be re-installed to reload their java dependancies. This can be achieved by executing the following commands: spool jvm_refresh.log connect / as sysdba @?/ord/admin/ordinst.sql; connect / as sysdba @?/ord/im/admin/iminst.sql; connect / as sysdba @?/ultrasearch/admin/wk0deinst.sql SYS change_on_install ""; connect / as sysdba @?/ultrasearch/admin/wk0install.sql SYS change_on_install change_on_install DRSYS TEMP "" PORTAL false; connect / as sysdba @?/dm/admin/dminst.sql ODM TEMP Procedure Has been tested line by line in njdbupora0-m /opt/oracle/9.2.0 **************************************************************************** *** Once applied successfully, test result shows following: Installed Patch List: ===================== Patch 3578419 applied on Tue Jun 08 15:23:47 EDT 2004 [ Base Bug(s): 3199908 ] /home/dba/opatch/opatch.pl version: 1.0.0.0.49 Copyright (c) 2001,2002,2003 Oracle Corporation. All Rights Reserved. OPatch succeeded.
7.1.2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Major Steps for Oracle 8174 Security Patch
List down all invalid objects before applying patch. g2disable/fmsdiable LONPFM01 Shutdown the oracle instances. Shutdown the listener Shutdown all other process if running by Oracle and using the ORACLE_HOME Check the process by using the fuser command Take the TAR backup of the ORACLE_HOME cd $ORACLE_HOME tar cvfh /local/oracle/LONPFM01/backup/cold/pre_sec_patch48_51.tar . Check /var/opt/oracle/oratab file and oraInst.loc Apply patch Start DB and listener. Check connectivity Check invalid objects. Update LONDBA
16 17
Update end user Close CR.
7.2 7.2.1
Up gradation
Oracle Database Upgrade from 8.1.7.0 32Bit to 8.1.7.4 64Bit with Java installed
Objective: Objective was to upgrade CAD1 database on host frankfurtdb1 from oracle 8.1.7.0 32Bit to 8.1.7.4 64 Bit. CAD1 database is having java installed so procedure for up-gradation was little different than non-java installed database. Steps are mentioned below which was carried out to complete the up-gradation. Steps to be followed: Phase-I: Upgrade Oracle 8.1.7.0 32 Bit to 8.1.7.4 32Bit Upgrade the database 8.1.7.0 32 Bit to 8.1.7.4 32Bit using the document available in the Metalink and steps prepared by offshore DBA (Only phase 1 need to be followed).
1) Metalink document is attached. 2) Document prepared by Offshore DBA is attached. Only Phase 1 needs to be followed for upgrading database from 8.1.7.0 32Bit to 8.1.7.4. 32Bit.
Phase-II: Upgrade Oracle 8.1.7.4 32 Bit to 8.1.7.4 64Bit 1) Follow the steps mentioned in the Metalink document ID 183649.1. Patch # 1994933 and 2209569 works for Oracle 8.1.7.4 also on Solaris 2.8 so do not get confused with the patch description. Patches have been copied in the global directory with patch_1994933 and patch_2209569. /home/oraclesw/server/patches/8.1.7/64Bit
Metalink document ID: 183649.1 are attached for your reference.
7.2.2
1. 2. 3. 4.
Procedure to migrate from Oracle 9i to Oracle 9.2.0.5
sqlplus /nolog exec dbms_stats.delete_schema_stats ('SYS'); exec dbms_stats.gather_schema_stats ('SYS'); shutdown server and listener change /var/opt/oracle/oratab to 9.2.0.5 login to host again so that ORACLE_HOME is set If JVM and XDK are enabled, you need to make sure that following parameters have atleast 150 MB. SHARED_POOL_SIZE JAVA_POOL_SIZE Start real upgrade (20 min) cd $ORACLE_HOME/dbs ln -s /local/oracle/${ORACLE_SID}/admin/pfile/init${ORACLE_SID}.ora . ln -s /local/oracle/${ORACLE_SID}/admin/pfile/orapw${ORACLE_SID} . start listener sqlplus /nolog connect / as sysdba startup migrate
5.
6.
spool patch.log @/opt/oracle/9.2.0.5/rdbms/admin/catpatch.sql spool off 7. Review error log and patch log. Fix any errors and rerun catpatch if need be. 8. Run the shutdown command. Run the startup command. 9. startup spool patch2.out @/opt/oracle/9.2.0.5/rdbms/admin/utlrp.sql 10. Now update /home/dba/files/.NP.cfg Change to correct value ORACLE_HOME=/opt/oracle/9.2.0 vi /home/dba/files/${ORACLE_SID}.NP.cfg
7.2.3
1
Procedure to Migrate from 8.1.7.4 to 9.2.0.3
Avoid running out of space during the migration: Prepare the system rollback segment: Alter rollback segment system storage (maxextents 121 next 1M); Ensure plenty of free space in the SYSTEM tablespace. A minimum of 150 Mb additional free space: Select max(bytes) from dba_free_space where tablespace_name='SYSTEM'; Ensure plenty of free space in the ROLLBACK tablespace. Ensure that you have at least 1 rollback segment of 70 Mb if the number of objects in the database exceeds 5000: Select count(*) from dba_objects;
If you run out of space in one of these tablespaces during the upgrade, you will need to perform the upgrade again. 2 Verify the certification of oracle 9i on the OS version you are using. Verify all necessary OS patches are installed. Example for Solaris: $ showrev -p 3 Upgrade will leave all objects (packages,views,...) invalid, except for tables. All other objects must be recompiled manually.List all objects that are not VALID before the upgrade. This list of fatal objects: Select substr(owner,1,12) owner, substr(object_name,1,30) object, Substr(object_type,1,30) type,status from dba_objects where status 'VALID'; To create a script to compile all invalid objects, before upgrading, run the the script called tlrp.sql in the $ORACLE_HOME/rdbms/admin directory. This script recompiles all invalid PL/SQL in the database including views. $ cd $ORACLE_HOME/rdbms/admin $ sqlplus sys/ as sysdba SQL> @utlrp.sql Run the script and than rerun the query to get invalid objects. spool invalid_pre.lst Select substr(owner,1,12) owner, Substr(object_name,1,30) object, Substr(object_type,1,30) type, status from dba_objects where status 'VALID'; spool off This last query will return a list of all objects that cannot be recompiled before the upgrade in the file 'invalid_pre.lst' . 4 Verify the kernel parameters according to the installation guide of the new version. Example for Solaris: $ cat /etc/system 5 Ensure ORACLE_SID is set to instance you want to upgrade. Echo $ORACLE_SID Echo $ORACLE_HOME 6 As of Oracle 9i the sql NCHAR datatypes will be limited to the Unicode character set encoding only (UTF8 and AL16UTF16). Any other NCHAR datatype will no longer be supported. When upgrading to 9i the value of the NCHAR is based on the NCHAR datatype used in the Oracle8 version. If the old National character set is UTF8, the new will be UTF8. Otherwise the national character set is changed to AL16UTF16. Verify character set of the database: $ Sqlplus SYS/ Select name, substrb(value$,1,40) value from props$; 7 Check for corruption in the dictionary, using the following commands in sqlplus connected as sys: Set verify off
Set space 0 Set heading off Set feedback off Set pages 1000 Spool analyze.sql Select 'Analyze '||object_type||' '||object_name ||' validate structure;' from dba_objects where owner='SYS' and object_type in ('INDEX','TABLE','CLUSTER'); spool off This creates a script called analyze.sql. Run the script. This script (analyze.sql) should not return any errors. 8 Ensure that all Snapshot refreshes are successfully completed, and replication is stopped. $ Sqlplus SYS/ Select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times; Stop the listener for the database $ lsnrctl Lsnrctl> stop Ensure no files need media recovery: $ sqlplus SYS/ Select * from v$recover_file; This should return no rows. Ensure no files are in backup mode: Select * from v$backup where status!='NOT ACTIVE'; This should return no rows. Resolve any outstanding unresolved distributed transaction: Select * from dba_2pc_pending; If this returns rows you should do the following: Select local_tran_id from dba_2pc_pending; Execute dbms_transaction.purge_lost_db_entry(''); Commit; Disable all batch and cron jobs. Ensure the users sys and system have 'system' as their default tablespace. Select username, default_tablespace from dba_users where username in ('SYS','SYSTEM'); To modify use: Alter user sys default tablespace SYSTEM; Alter user system default tablespace SYSTEM; Optionally ensure the aud$ is in the system tablespace when auditing is enabled. Select tablespace_name from dba_tables where table_name='AUD$'; Note down where all control files are located. Select * from v$controlfile;
9
10
11
12
13 14
15 16
17
Note down all sysdba users. Select * from v$pwfile_users; If a passwordfile is used copy it to the new location. On unix the default is $ORACLE_HOME/dbs/orapw. On windows NT this is %ORACLE_HOME%\database\orapw Shutdown the database $ sqlplus SYS/ SQL> Shutdown immediate Change the init.ora file: Make a backup of the init.ora file. Verify that the parameter DB_DOMAIN is set properly. Ensure there is a value for DB_BLOCK_SIZE . Comment out the JOB_QUEUE_PROCESSES parameter, put in a new and set this explicitly to zero, during the upgrade. Comment out the AQ_TM_PROCESSES parameter, put in a new and set this explicitly to zero, during the upgrade. If archiving is enabled set LOG_ARCHIVE_START=TRUE. Ensure that the USER_DUMP_DEST, BACKGROUND_DUMP_DEST and the CORE_DUMP_DEST are set to an explicit directory. Set the parameter _SYSTEM_TRIG_ENABLED explicitly to FALSE during the upgrade. Set the parameter OPTIMIZER_MODE to CHOOSE during the upgrade Comment out obsoleted parameters(list in appendix A). Comment out SNAPSHOT_REFRESH_? Parameters. Either leave COMPATIBLE unset in your initialization parameter file or set COMPATIBLE to 8.1.x. Setting this parameter a lower or a higher value than 8.1.X results in an error during the upgrade. If you are using a passwordfile set the parameter REMOTE_LOGIN_PASSWORDFILE=NONE If you have set the parameter NLS_LENGTH_SEMANTICS to CHAR, change the value to BYTE during the upgrade. If you are using a cluster database, set the parameter CLUSTER_DATABASE=FALSE during the upgrade. If the parameter RESOURCE_MANAGER_PLAN is set, comment it out as a workaround for bug 2610769 .
18
19
20 21 22
Check for adequate freespace on archive log destination file systems. Ensure the NLS_LANG variable is set correctly: $ echo $NLS_LANG If needed copy the listener.ora and the tnsnames.ora to the new location (when no TNS_ADMIN env. Parameter is used). cp $ORACLE_HOME/network/admin /network/admin If needed copy the init.ora file to the new oracle_home or Create a link to the init.ora. cp $OLD_ORACLE_HOME/dbs/init.ora $NEW_ORACLE_HOME/dbs/init.ora OR Ln ?s /init/ora/file/path/init.ora $ORACLE_HOME/dbs/init.ora
23
Also check 'ifile' parameters in the init.ora, to be set to the correct file. If an IFILE is used, verify the above mentioned parameter for the init.ora and copy this to the correct location. Change the IFILE entry in the init.ora file when this file changes from location. 24 25 26 Update the oratab entry, to set the new ORACLE_HOME and disable automatic startup: ::N Update the environment variables like ORACLE_HOME and PATH $ . oraenv Make sure the following enviroment variables point to the new Release directories: - ORACLE_HOME - PATH - ORA_NLS33 - ORACLE_BASE - LD_LIBRARY_PATH - ORACLE_PATH For HP-UX systems verify the SHLIB_PATH parameter points to the new release directories. $ env | grep ORACLE_HOME $ env | grep PATH $ env | grep ORA_NLS33 $ env | grep ORACLE_BASE $ env | grep LD_LIBRARY_PATH $ env | grep ORACLE_PATH HP-UX: $ env | grep SHLIB_PATH Run the upgrade script: $ cd $ORACLE_HOME/rdbms/admin Sqlplus /nolog SQL> Connect sys/passwd_for_sys as sysdba Use Startup MIGRATE when you are upgrading to Oracle 9.2: SQL> Startup Migrate Spool the output so you can take a look at possible errors after the upgrade: SQL> Spool Upgrade.log Run the appropriate script for your version. From To: Only Script to Run ==== === ================== 8.0.6 9.0.1 u0800060.sql 8.0.6 9.2 u0800060.sql 8.1.5 9.0.1 u0801050.sql 8.1.5 9.2 Not Supported 8.1.6 9.0.1 u0801060.sql 8.1.6 9.2 Not Supported 8.1.7 9.0.1 u0801070.sql 8.1.7 9.2 u0801070.sql 9.0.1 9.2 u0900010.sql Use this Script--8.1.7 9.2 u0801070.sql
27
Each of these scripts is a direct upgrade path from the version you are on to 9i. You do not need to run catalog.sql and catproc.sql as these two scripts are called from within the upgrade script. Display the contents of the component registry to determine which components need to be upgraded: SQL> Select comp_name, version, status from dba_registry; Run the script cmpdbmig.sql to upgrade the components which can be upgrade with the SYSDBA privilege (This step is only valid for upgrades towards 9.2): SQL> @cmpdbmig.sql The components upgraded by this script are:
Jserver JAVAVM, oracle XDK for Java, Oracle 9i RAC, Oracle Data Mining, OLAP analytical Workspace, Oracle 9i Java Packages, Messaging Gateway, Oracle Workspace Manager, OLAP Catalog, Oracle Label Security.
Display the components which were upgraded: SQL> Select comp_name, version, status from dba_registry; End the spool of the upgrade: SQL> Spool Off 28 Restart the database: SQL> Shutdown Immediate (DO NOT USE SHUTDOWN ABORT!!!!!!!!!) SQL> Startup restrict Executing this clean shutdown flushes all caches, clears buffers and performs other database housekeeping tasks. Which is needed if you want to upgrade specific components. Run script to recompile invalid pl/sql modules: SQL> @utlrp If there are still objects which are not valid after running the script run the following: spool invalid_post.lst Select substr(owner,1,12) owner, Substr(object_name,1,30) object, Substr(object_type,1,30) type, status from dba_objects where status 'VALID'; spool off Now compare the invalid objects in the file 'invalid_post.lst' with the invalid objects in the file 'invalid_pre.lst' you create in step 5. Edit init.ora file: Leave the parameter JOB_QUEUE_PROCESSES=0 Leave the parameter AQ_TM_PROCESSES=0 Remove the parameter _system_trig_enabled from the init.ora file. This parameter was explicitly set to false during the upgrade. Modify the log_archive_dest parameter specify only the path, but make sure it ends with a '/'. (remove the format) e.g. log_archive_dest=/path/arch into log_archive_dest=/path/ Modify the parameter log_archive_format and add the format previously removed from the log_archive_dest. E.g log_archive_format=arch%t_SID_%s.log If you are using a password file set the REMOTE_LOGIN_PASSWORDFILE parameter to the value used before migration.
29
30
31
Shutdown the database and startup the database. $ sqlplus /nolog SQL> Connect sys/passwd_for_sys as sysdba SQL> Shutdown SQL> Startup restrict In Oracle9i, the National Character Set (for NCHAR, NVARCHAR2, and NCLOB datatypes) will be limited to the Unicode character sets AL16UTF16 and UTF8 only. When upgrading to Oracle9i, the National Character Set will be chosen based on the previous National Character Set. If your previous National Character Set was UTF8, then it will remain UTF8 in Oracle9i. All other National Character Sets will be converted to AL16UTF16. To upgrade user tables with NCHAR datatype columns run the script utlnchar.sql: $ sqlplus /nolog SQL> connect sys/passwd_for_sys as sysdba SQL> @utlnchar.sql You can override the default upgrade selection of the National Character Set. That is, a version 8 UTF8 National Character Set can be converted to an Oracle9i AL16UTF16 National Character Set by running the script n_switch.sql: SQL> @n_switch.sql After running utlnchar.sql and alternatively n_switch.sql. Shut down the database cleanly. SQL> shutdown immediate Now edit the init.ora: Put back the old value for the JOB_QUEUE_PROCESSES parameter Put back the old value for the AQ_TM_PROCESSES parameter If you change the value for NLS_LENGTH_SEMANTICS prior to the upgrade put the value back to CHAR. If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE . Create a server parameter file with a initialization parameter file SQL> Create spfile from pfile; This will create a spfile as a copy of the init.ora file located in the $ORACLE_HOME/dbs directory. Modify the listener.ora file: For the upgraded intstance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME. Start the listener $ lsnrctl LSNRCTL> start Enable cron and batch jobs Change oratab entry to use automatic startup SID:ORACLE_HOME:Y To use the new features in 9i change the compatible parameter to the new release. When everything is well tested, update the compatible parameter in the init.ora and restart to the new release number. COMPATIBLE=9.0.X where x is the release number.
32
33
34
35
36
37 38 39
7.2.4
1
Upgrading the JAVA ENGINE (Only for upgrades to 9.0.1)
Check if java has previously been installed: Run the following query: select count(*) from dba_objects where object_type like 'JAVA%'and owner = 'SYS'; If this is >0 then java has been installed. If this value =0 java is not installed and there is no reason to upgrade the java engine. Restart the database: $ Sqlplus /nolog SQL> connect sys/passwd_for_sys as sysdba SQL> Shutdown immediate SQL> startup restrict Run the appropriate script depending on what release you are coming from: $ cd $ORACLE_HOME/javavm/install Sqlplus /nolog SQL> Connect sys/passwd_for_sys as sysdba SQL> Spool catoutjava.log From Only Script to Run - 8.1.5 jvmu815.sql - 8.1.6 jvmu816.sql - 8.1.7 jvmu817.sql After you have run this script, all user classes are invalid. These become implicitly valid when you executed them. You can explicitly revalidate them when executing the following command: SQL> Alter Java Class resolve; Restart the database: $ Sqlplus /nolog SQL> connect sys/passwd_for_sys as sysdba SQL> Shutdown immediate SQL> startup restrict
2
3
4
7.2.5
Case Study: Refresh and Migration to 9.2.0.3
The task COPY FROM NJEXPPRD to NYEXPDM1 involves migration. Back ground: SOURCE is NJEXPPRD on host njoraprd1 on 8.1.7.3.0-filesystem/64bit. TARGET is NYEXPDM1 on host njoradev1 on 9.2.0.3.0-filesystem/64bit. Work Plan: 1 2 3 4 Wipe out the exising DB attached to NYEXPDM1 on host njoradev1. Transfer backup, arch and LogAterDump files from the source to the target host to appropriate NJEXPDM1 locations. Link from NJEXPPRD to NYEXPDM1 needs to be created on the target host under /local/oracle for refresh. On the target host njoradev1 initially NJEXPPRD was recovered from the backup of the prod via 8.1.7.3.0 binaries. Then renamed the recovered NJEXPPRD DB on njoradev1 to NYEXPDM1 DB on 8.1.7.3.0.
5
Then migration was done from 9i home on njoradev1 /opt/oracle/9.2.0.3 for NYEXPDM1 and the migration ran successfully. Logs are available at $ORACLE_HOME/rdbms/admin.
Migration and Post Migration steps were carried out as per the metalink note 159657.1"Complete Upgrade CHECKLIST for manual Upgrades from 8.x to 9i." All steps as listed in the note was followed. Following is worth noting: The post migration step of upgrading user tables if they have NCHAR datatypes is SKIPPED, because NYEXPDM1 DOES NOT have any user tables with NCHAR datatype. SQL> 1 select table_name, column_name, data_type , owner from dba_tab_columns where 2* data_type='NCHAR' TABLE_NAME COLUMN_NAME DATA_TYPE OWNER ------------------------- ------------------------------ ---------- ------ALL_REPPRIORITY NCHAR_VALUE NCHAR SYS DBA_REPPRIORITY NCHAR_VALUE NCHAR SYS USER_REPPRIORITY NCHAR_VALUE NCHAR SYS REPCAT$_PRIORITY NCHAR_VALUE NCHAR SYSTEM The only invalid object is the following, which can be addressed as the cause is known. 1* select count(*), object_type, owner from dba_objects where status!='VALID' GROUP BY OBJECT_TYPE, OWNER SQL> / COUNT(*) OBJECT_TYPE OWNER ---------- ------------------ -----------------------------1 PACKAGE BODY PERFSTAT Paged and mailed end user sreddypa regarding the closure of the tkt/task. Note: Client CONNECTIVITY CHECKED. Works Fine.-(confirms--Entry for NYEXPDM1 exists in tnsnames.ora) The DB NYEXPDM1 is not configured in DataCompass as this DB is being used by end -users for some tetsing purpose currently. Pass word for thesystem user of NYEXPDM1 is the same as the source. To add to the info, the archival log sequence numbers used for the refresh of NYEXPDM1 from NJEXPPRD are 30517-------->30630. Have cleaned the arch and LogAfterDump dir after the recovery.
8
Database Maintenance Task
8.1 Procedure to request SA to create raw partition – Template
The Request for adding space to tablespaces will be raised by the user of the Applicaion/Database . If the Database is a Development/QA /STG database then the user will raise a NYH ticket to the Oracle DBA group and in turn the DBA will raise a SA ticket to the unix group for creating the raw partiotions on the particular host for the particular database If the database is a Production Database then the user will create a NYC ticket ie change ticket to the Oracle DBA group and in turn the DBA will get the approval from the DBA manager example gajanan hedge and will raise a NYH ticket to the unix SA group ie ( AM-UNIXSA-PROD) Steps involved in opening a SA ticket for Creating raw partitions by DBA For example if the user has asked for adding space to the tablespace
NETTRACKER_DATA1 : 5GB for the database EPIPROD on the host njecomdb2-m This is a Production Database, so atmost Caustion should be taken 1. Please go into the Database and find out the latest Datafile which is added to the tablespace 2. If the latest datafile added is /dev/vx/rdsk/epiprod_datadg/nettracker_data1_23 then request the SA to create a raw partition with the next number for the Datafile whici is as follows 3. /dev/vx/rdsk/epiprod_datadg/nettracker_data1_24 5005MB Point ot be noted here is that the raw partition has to biven few MB’s more than the required size so that the user asked space can be added to the tablespace ie in this case the user has requested 5GB of space , so Raw partition has to be created for 5005 MB else if the raw partition is created for 5000MB then less than 5GB of space can only be added to the tablespace instead of the user requested 5 GB. 4. Once the raw partion is created then the DBA has to create a link to the raw partition with the whole path of the datafile that has to be added to the tablespace. Example ln –s /dev/vx/rdsk/epiprod_datadg/nettracker_data1_24 nettracker_data1_24.dbf 5. DBA has to add the space after the step 4 has been completed and after verifying the same. IMPORTANT FACTOR TO BE NOTED While creating tickets for raw partions on Primary database please verify if the primary production database has an standby database in place if yes then all the step mentioned above should be done for the Standby database also , else the the standby database will be out of sync with the primary and this in turn will create the latency problems. Example of the Tickets created by the user for addition of tablespace and the ticket created by the DBA to the SA Group fro creating raw partitions User request --------------------can we assign more space to the following tablespaces:NETTRACKER_DATA1 : 5GB DIM_MEDIUM_IDX1 : 5GB DIM_LARGE_DATA1 : 5GB
DBA ticket to the SA Host njecomdb2-m Please create RAW Partitions owned by oracle:dba
Hostname: njecomdb2-m ******************************** Partition Name Size in MB /dev/vx/rdsk/epiprod_datadg/nettracker_data1_24 5005MB /dev/vx/rdsk/epiprod_datadg/dim_medium_idx1_37 5005 MB /dev/vx/rdsk/epiprod_datadg/dim_large_data1_27 5005 MB Please change the owner:group of the above partitions as oracle:dba.
8.2 8.2.1
Procedure to Add Space to the Tablespace
Adding Tablespaces or Datafiles to the Primary Database
Adding a tablespace or datafile to the primary database generates redo that, when applied at the standby database,automatically adds the datafile name to the standby control file. If the standby database locates the file with the filename specified in the control file, then recovery continues. If the standby database is unable to locate a file with the filename specified in the control file, then recovery terminates. Perform one of the following procedures to create a new datafile in the primary database and update the standby database. Note that if you do not want the new datafile in the standby database, you can take the datafile offline manually using the following syntax: SQL> ALTER DATABASE DATAFILE 'filename' OFFLINE DROP; To add a tablespace or datafile to the primary database and create the datafile in the standby database: 1) Create a tablespace on the primary database as usual. For example, to create new datafile t_db2.f in tablespace tbs_2, issue: SQL> CREATE TABLESPACE tbs_2 DATAFILE 't_db2.f' SIZE 2M; If the standby database is shut down, start the standby instance without mounting it. For example, enter: SQL> STARTUP NOMOUNT pfile=/private1/stby/initSTANDBY.ora If the standby database is currently in managed recovery mode, skip to step 4. Mount the standby database, then place it in managed recovery mode: SQL> ALTER DATABASE MOUNT STANDBY SQL> RECOVER MANAGED STANDBY DATABASE; DATABASE;
2)
3)
4)
Switch redo logs on the primary database to initiate redo archival to the standby database: SQL> ALTER SYSTEM SWITCH LOGFILE; If the recovery process on the standby database tries to apply the redo containing the
CREATE TABLESPACE statement, it stops because the new datafile does not exist on the standby site. 5) Either wait for the standby database to cancel recovery because it cannot find the new datafile, or manually cancel managed recovery: SQL> RECOVER MANAGED STANDBY DATABASE CANCEL; Note that CREATE TABLESPACE redo adds the new filename to the standby control file. The following alert.log entry is generated: WARNING! Recovering datafile 2 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. Successfully added datafile 2 to media recovery Datafile #2: '/private1/stby/t_db2.f' 6) Create the datafile on the standby database. For example, issue: SQL> ALTER DATABASE CREATE DATAFILE '/private1/stby/t_db2.f' AS '/private1/stby/t_db2.f'; Place the standby database in managed recovery mode: SQL> RECOVER MANAGED STANDBY DATABASE;
7)
Continue normal processing on the primary database. The primary and standby databases are now synchronized.
8.3
Database schema setup
The database schema setup involves lot of things from the user perspective 1. The Approval should be got from the Lehman DBA manager if the database is a Production database Need to get the following Informations from the User The schema name / password to be given The tablespace sizes for both Indexfiles and datafiles IF the Tablespace exist , then there is no need of creating new tablespaces else need to create the tablespaces before creating the users. The grants and privileges that has to be given to the user. Example of User creation CREATE USER ESERVLOAD IDENTIFIED BY 'xxxxxxxxxx' DEFAULT TABLESPACE ADB_DATA TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON ADB_DATA PROFILE DEFAULT ACCOUNT UNLOCK
/ GRANT SELECT_CATALOG_ROLE TO ESERVLOAD / ALTER USER ESERVLOAD DEFAULT ROLE SELECT_CATALOG_ROLE / GRANT CREATE ANY SEQUENCE TO ESERVLOAD / If the user specifies the Data to be imported to the schema, then the DBA should export the specified data from the database/schema mentioned by the user and import the same to the new user as mentioned by the user. Then the DBA has to send the TNS entry to the user so as to enable the user to connect Through sqlnet to the schema and the database under which the schema is created
8.4 Database Analyze task (Manual/Automated)
The analyze task can be done both Manually and it can also be automated, here in Lehman Brothers , the Database Analyze is Automated /Scheduled through Autosys tool For Database Analyze task the user has to provide the following inputs to the DBA for Scheduling the Same Start time of the Task Day/Days of the week that the task has to be run Conditions if any which has to be satisfied for this task to be run successfully. Based on the above inputs the DBA will have to raise a ticket to the PBA_SCHED team to Input /Schedule the CODE (JIL) in the Autosys Which will be in turn Monitored by the PBA_MON group for any errors in the running of the JOB The Analyze job Consists of two parts one the Removal of the tasks done /previous day before running the current day‟s analyze, if this job fails then the Analyze job will also fail The Code /JIL which is in place for the analyze job is as follows /* ----------------- DBAORADBANALYZE_ETGSTG_Trmtask ----------------- */ insert_job: DBAORADBANALYZE_ETGSTG_Trmtask job_type: c box_name: DBAORADBANALYZE_Box command: rm -f /home/dba/tasks/ETGSTG_T/analyze/* machine: njecomdb11-m #owner: oracle permission: gx,wx
date_conditions: 1 days_of_week: mo,tu,we,th,fr start_times: "19:30" description: "Run DBAnalyze" std_out_file: $DbAnalyzeLogs/ETGSTG_T/DBAnalyze.ETGSTG_T.`/bin/date +\%y\%m\%d ` std_err_file: $DbAnalyzeLogs/ETGSTG_T/DBAnalyze.ETGSTG_T.`/bin/date +\%y\%m\%d ` alarm_if_fail: 0 profile: /home/dba/files/DBAAnalyzeBatchProfile.sh
/* ----------------- DBAORADBANALYZE_ETGSTG_T ----------------- */ insert_job: DBAORADBANALYZE_ETGSTG_T job_type: c box_name: DBAORADBANALYZE_Box command: /home/dba/bin/AnalyzeCT50_OWNER.ksh -S ETGSTG_T -d ETGSTG_T m"ora_db a@lehman.com" machine: njecomdb11-m #owner: oracle permission: gx,wx condition: success(DBAORADBANALYZE_ETGSTG_Trmtask) description: "Run DBAnalyze" std_out_file: $DbAnalyzeLogs/ETGSTG_T/DBAnalyze.ETGSTG_T.`/bin/date +\%y\%m\%d ` std_err_file: $DbAnalyzeLogs/ETGSTG_T/DBAnalyze.ETGSTG_T.`/bin/date +\%y\%m\%d ` alarm_if_fail: 0 profile: /home/dba/files/DBAAnalyzeBatchProfile.sh
8.5
Database Reindex (Manual/Automated)
The Database Reindex can be done two ways, ie manually and can also be automated At lehman the database reindex is automated and scheduled through Autosys The user needs to give the following Start_Times Day/days of the week that the job has to run
User groups that have to be informed once the Database reindex job completed. After getting the required info, DBA will raise an PBA_SCHED ticket to schedule the same, and PBA_MON will monitor the job for error and other problems The Sample JIL /code for the Autosys job /* ----------------- DBAMAINTORA_CAMEORebuildIdx ----------------- */ insert_job: DBAMAINTORA_CAMEORebuildIdx job_type: c box_name: DBAMAINTORA_CAMEOWkndBox command: /home/dba/bin/DBRebuildIndex.ksh -S CAMEO -D CAMEO -m"ora_dba" machine: njcameoprod1 #owner: oracle permission: date_conditions: 1 days_of_week: su start_times: "12:00" description: "run Reindex on CAMEOPROD1" std_out_file: /home/dba/log/DBReindex.CAMEOPROD1.log.`/bin/date +\%y\%m\%d ` std_err_file: /home/dba/log/DBReindex.CAMEOPROD1.log.`/bin/date +\%y\%m\%d ` alarm_if_fail: 1
8.6
Database Spacereport
The Space report for Databases in Lehman Environment is Automated through Autosys The user need to give the following inputs to the DBA to schedule the job for space report and raise a remedy ticket for the scheduling the same to the PBA_SCHED team The user should give the Start_times , Day/Days of the week that the report should run The email/contact info of the user groups to which the Space report should be sent. Sample of the code /JIL that is submitted to run the job /* ----------------- DBAORASPACEREPORT_EPIPROD ----------------- */ insert_job: DBAORASPACEREPORT_EPIPROD job_type: c box_name: DBAORASPACEREPORT_Box command: /home/dba/bin/RunSpaceReport.ksh EPIPROD "ora_dba@lehman.com,leads@lehman.com" machine: njecomdb2-m #owner: oracle permission: gx, wx date_conditions: 1
days_of_week: all start_times: "08:30" description: "Space report" std_out_file: /home/dba/log/DBExtentReport.EPIPROD.log std_err_file: /home/dba/log/DBExtentReport.EPIPROD.log alarm_if_fail: 0
8.7
Purgelog tasks
The Purgelogs for Databases in Lehman Environment is Automated through Autosys The user need to give the following inputs to the DBA to schedule the job for space report and raise a remedy ticket for the scheduling the same to the PBA_SCHED team The purge logs job will remove the purge the archive log files, trace files in the cdump.udump directories The user should give the Start_times , Day/Days of the week that the report should run Sample of the code /JIL that is submitted to run the job /* ----------------- DBAORAPURGELOGS_EPIPROD ----------------- */ insert_job: DBAORAPURGELOGS_EPIPROD job_type: c box_name: DBAORAPURGELOGS_Box command: /home/dba/bin/RunPurgeLogs.ksh EPIPROD machine: njecomdb2-m #owner: oracle permission: gx, wx description: "Clean up old logs and traces" std_out_file: /home/dba/log/RunPurgeLogs.log std_err_file: /home/dba/log/RunPurgeLogs.log alarm_if_fail: 0
8.8
Startup and Shutdown using oractl and other scripts and their configurations
The Oracle Startup and shutdown scripts for shutting down the Oracle Production databases are in the following directory /home/dba/bin directory on the each of the hosts where the production databases exists
DBA needs to give the Database name as an input parameter for shutting down the server Shutdown.ksh IMPORTANT ASPECT TO BE NOTED If the Host is under VCS and if the databases are covered under the VCS then the DBA cannot shutdown the Database just like that, he needs to take the help of the SA or if he has access to VCS console , needs to the following DBA /SA need to freeze the Service group Then bring the server down through VCS And clear the VCS faults once the service group is up online
8.9 Procedure to setup the Top SQL report
For setting up the TOP SQL report , the user needs to give the Time Interval at which the report has to be run and also the Database name for which this has to be run.. The Top SQL report for all the Production Data Servers is being run from the DBA Central Database NJDBAPROD through Crontab. Example : 0 9,10,11,12,13,14,15,16,17 * * * /opt/app/mon/bin/TopSQLReport_html2.ksh -SMMPP RD -h1 m"ora_dba@lehman.com,nkrishna@lehman.com,alicciar@lehman.com,SYim@lehman .com" >> /opt/app/mon/log/TopSQLReport_html.MMPPRD 2>&1
8.10
Procedure to setup the row count
The Row count report is not implemented on all the production databases and for all the tables. The report is implemented only for a few tables as requested by the users in a few production databases The Report is implemented for the database NJLPFPRD which is primary database Here is the crontab entry for the same, It is being run from the same host where the Database resides. 0 6 * * 1-5 /home/dba/bin/GetRowCounts.ksh NJLPFPRD report_only LPF >> /home/dba /log/GetRowCounts.NJLPFPRD.report.log 2>&1
8.11
Procedure to manually synchronize to Standby Latency
The Standby database can be synchronized manually , the process involved is as follows 1. DBA needs to login to the primary database and check the latest archivelog number generated and also login to the standby database and verify the archive logs applied and also verify if any archive logs application is missing in the standby database 2. If applying of one of the archivelogs is missed in the standby database ,then the standby database will be out of sync with the Primary database and due to this the latency problem arises To resolve the latency problem DBA needs to do the following Find out the missing log in the standby database from the alert log of the standby database Copy the log from the primary database to the Standby archive log location Cancel the managed standby recover of the standby database Alter Database recover managed standby database cancel; Recover the standby database manually Recover standby database ; After recovery the process exits by itself Then put the standby database in Auto recovery mode by using the script which is under the /home/dba/bin directory for all the databases on respective hosts. The input parameter , the database name should be given and and suffix the command with & so that the process runs in the background without ant interuption ./ SetRecoveryMode.ksh & ./ SetRecoveryMode.ksh Test & Switch the archive logs in the Primary and verify if the logs have been transferred to the Standby and applied.
8.12
Procedure to purge statspack report
The Statspack purge is related to the TOOLS tablespace of the respective databases and also the Perfstat user in the database , whenever the tools tablespace becomes full , in turn generates the cannot extend tablespace tools perfstat error The statspack purge process is Automated through Autosys and is the recreation of the Statspack and the script is under /home/dba/bin/ install_statspack.ksh Need to provide the Database name as an input to the script for the installation/purging of Statspack Example of the Statspack Installation through Autosys is as follows
/* ----------------- DBAORAPURGESTATS_CON4PRD ----------------- */ insert_job: DBAORAPURGESTATS_CON4PRD job_type: c box_name: DBAORAPURGESTATS_Box command: /home/dba/bin/install_statspack.ksh CON4PRD machine: dbu02pora02 #owner: oracle permission: gx, wx description: "Recreate statspack on dev DBs" std_out_file: /home/dba/log/RunPurgestats_CON4PRD.log std_err_file: /home/dba/log/RunPurgestats_CON4PRD.log alarm_if_fail: 0
8.12.1.1 Recreating Statspack
Recreation Using Script: Njoradev1: /home/dba/bin/recrstatspack.ksh for 9i DBs by JP Manual Recreation: @/home/dba/sql/freespace.sql Select * from dba_jobs; Note jobno for perfstat Following as perfstat Exec dbms_job.remove(job_no); As sys as sysdba @$ORACLE_HOME/rdbms/admin/spdrop.sql @$ORACLE_HOME/rdbms/admin/spcreate.sql @$ORACLE_HOME/rdbms/admin/spauto.sql
@/home/dba/sql/freespace.sql Please note all 9i Statspack SQLs like sp* can still be run on 8i DBs.
8.13
Re-org Activity
8.13.1 Procedure to identify the objects which need to be re-org 8.13.2 Identify the tablespaces (8i) which need to be re-org
Identify the tablespaces which is fragmented the most and which requires the re-org to be done . This reorg process helps in Improving the performance of the database and also saves lot of unutilized space which can be allocated to other tablespaces which are in need of space The fragmentation details for a particular database can be got by executing an SQL script given below
i. Fragmentation select tablespace_name, round(bytes/1024/1024) "size of Fragments in MB" , count(*) "No of Fragments" from dba_free_space where tablespace_name in (select distinct tablespace_name from dba_segments where owner='SYSADM') group by tablespace_name,round(bytes/1024/1024);
8.13.3 Procedure to re-org the tablespaces to defragmentation
To re-org the tablespace of the database for defragmentation need to do the following Oncall Dba should inform the users of the Database that the tablespaces will be rebuilt and so the database will not be available for access The following tablespace re-org is taken as example. And the process is explained as below
Steps & Scripts a) b) c) Take export backup of database with rows=n indexes=y constraints=y grants=y Page batch to put the Autosys job DBAMAINTORA_NYRAPPRDAnalyze on ICE so that it will not start. Make a note of all invalid objects for the user 'CMTE' to make sure rebuild has no impact
SET PAGESIZE 50 LINESIZE 120 col object_name for a30 col object_type for select object_name, object_type ,status from dba_objects where status='INVALID' and owner='CMTE'; d) Generate rebuild script SET PAGESIZE 300 LINESIZE 120 Spool rebuild_CMTE_index.sql
SELECT 'ALTER INDEX CMTE.'||INDEX_NAME||' REBUILD ONLINE;' FROM DBA_INDEXES WHERE owner = 'CMTE' and tablespace_name='CMTE_OMS_INDEX' / SPOOL off
e) Run the Script SQL> set showmode on echo on SQL> @rebuild_CMTE_index.sql f) Generate rebuild script /* To move all the indexes housed in CMTE_OMS_DATA to CMTE_OMS_INDEX */ SET PAGESIZE 300 LINESIZE 120 Spool rebuild_CMTE_data_index.sql SELECT 'ALTER INDEX CMTE.'||INDEX_NAME||' REBUILD ONLINE Tablespace CMTE_OMS_INDEX nologging;' FROM DBA_INDEXES WHERE owner='CMTE' and tablespace_name='CMTE_OMS_DATA' / SPOOL off
g) Run the Script SQL> set showmode on echo on SQL> @rebuild_CMTE_data_index.sql h) Look for UNSUABLE indexes after the rebuild. SET PAGESIZE 50 LINESIZE 120 spool invalid_indexes.sql SELECT 'ALTER INDEX CMTE.'||INDEX_NAME||' REBUILD TABLESPACE CMTE_OMS_INDEX nologging ;' FROM DBA_INDEXES WHERE OWNER='CMTE' and status='UNUSABLE' / spool off i) If above activity has been completed before 12:00PM then no need to start the analyze because Autosys job will analyze the objects. Page batch to put the job DBAMAINTORA_NYRAPPRDAnalyze OFF-ICE. If above activity has been completed after 12:00PM then page batch to start the job DBAMAINTORA_NYRAPPRDAnalyze . **** Note: Please monitor job DBAMAINTORA_NYRAPPRDAnalyze if it runs after batch is paged. j) DBA notify users that indexes rebuild is done. k) users will check NYRAPPRD application and confirm with DBA. Script for Compute the indexes manually if required -------------------------SET PAGESIZE 50 LINESIZE 120 SPOOL rebuild_CMTE_analyze_index.sql SELECT 'anlyze INDEX CMTE.'||INDEX_NAME||' compute statistics;' FROM DBA_INDEXES WHERE owner = 'CMTE' and tablespace_name='CMTE_OMS_INDEX' / SPOOL off Script for ESTIMATE ( If Analyze has to be started after After 6 PM) ESTIMATE the statistics with sample 20 percent . ----------------------------SET PAGESIZE 50 LINESIZE 120 SPOOL rebuild_CMTE_estimate_index.sql SELECT 'anlyze INDEX CMTE.'||INDEX_NAME||' estimate statistics sample 20 percent;' FROM DBA_INDEXES WHERE owner = 'CMTE' and tablespace_name='CMTE_OMS_INDEX' /
SPOOL off
8.13.4 Procedure to re-org the objects 8.13.4.1
1)
Reorganize the tables to get rid of chained rows
/as sysdba a) b) c)
2)
Ensure that RMAN backup is successfully done. Run this thrice Run this thrice Alter system switch logfile; d) Kill sessions of TAP_OWNER on TAPPRD and Lock the use e) Run this thrice Alter system switch logfile; Export the tables and Keep a backup -- ETA - 3 Mins exp parfile=exp_tap_owner_tables.par;btell TAPPRD "Export of tables Done " a) Create temporary tablespace for Reorg. create tablespace TAP_DATA_TEMP DATAFILE '/local/oracle/TAPPRD/oradata1/tap_data_temp.dbf' size 600M extent management local uniform size 512k logging online segment space management auto;
3)
Make a note of Pre Image. set pagesize 400 linesize 120 col table_name for a20 col index_name for a30 col owner for a20 SELECT owner,index_name,table_name,status FROM DBA_indexes WHERE table_name in ('APPLICATION', 'DISK', 'HOST', 'HOST_PACKAGE', 'PERSONALITY') and owner='TAP_OWNER' order by 3; set pagesize 400 linesize 120 col segment_name for a35 col segment_type for a10 col owner for a20 SELECT owner,segment_name,BYTES/1024/1024 mb FROM DBA_SEGMENTS WHERE SEGMENT_name in
Indexes
Table Sizes
('APPLICATION', 'DISK', 'HOST', 'HOST_PACKAGE', 'PERSONALITY') and owner='TAP_OWNER'; Chained Rows Image truncate table chained_rows; SELECT 'Analyze table '||owner||'.'||segment_name||' list chained rows;' FROM DBA_SEGMENTS WHERE SEGMENT_name in ('APPLICATION', 'DISK', 'HOST', 'HOST_PACKAGE', 'PERSONALITY') and owner='TAP_OWNER'; select table_name,count(*) from chained_rows group by table_name; Count of objects SPOOL obj_count.lst select count(1),owner,object_type,status from dba_Objects group by owner,object_type,status; spool off Invalid Objects col object_name for a30 col object_type for a13 col owner for a10 set pagesize 100 linesize 120 select object_type,OWNER,OBJECT_NAME,status FROM DBA_OBJECTS WHERE OWNER='TAP_OWNER' AND STATUS ='INVALID' order by 1 ; col object_name for a30 col object_type for a13 col owner for a10 set pagesize 100 linesize 120 select Trigger_name,table_name,status FROM DBA_triggers WHERE OWNER='TAP_OWNER' and status 'ENABLED' order by 1 ; 4) Take a count of the tables. DECLARE
cursor c1 is select owner,table_name from dba_tables where owner='TAP_OWNER' AND table_name in ('APPLICATION', 'DISK', 'HOST', 'HOST_PACKAGE', 'PERSONALITY') order by 1,2; tblname varchar2(50); cnt number; stmt varchar2(500); ownr varchar2(25); tbl varchar2(50); begin stmt := 'truncate table sys.table_count_b4_reorg'; execute immediate stmt; for i in c1 loop tbl:=i.table_name; ownr:=i.owner; tblname := i.owner||'.'||i.table_name; --dbms_output.put_line(tblname); stmt := 'select count(*) from '||ownr||'.'||tbl||''; --stmt := 'select count(*) from "'||tblname||'"'; --dbms_output.put_line(stmt); execute immediate stmt into cnt; insert into sys.table_count_b4_reorg values(i.owner,i.table_name,cnt); --dbms_output.put_line(tblname); commit; end loop; end; / 5) Move to temporary tablespace SELECT 'Alter table '||owner||'.'||segment_name||' move tablespace TAP_DATA_TEMP pctfree 25;' FROM DBA_SEGMENTS WHERE SEGMENT_name in ('APPLICATION', 'DISK', 'HOST', 'HOST_PACKAGE', 'PERSONALITY') and owner='TAP_OWNER'; Move back to original tablespace SELECT 'Alter table '||owner||'.'||segment_name||' move tablespace TAP_DATA pctfree 25;' FROM DBA_SEGMENTS WHERE SEGMENT_name in ('APPLICATION', 'DISK', 'HOST', 'HOST_PACKAGE',
6)
'PERSONALITY') and owner='TAP_OWNER'; 7) Rebuild all indexes for these 5 tables set pagesize 400 linesize 120 col table_name for a20 col index_name for a30 col owner for a20 SELECT 'Alter index '||owner||'.'||index_name|| ' rebuild ;' FROM DBA_indexes WHERE table_name in ('APPLICATION', 'DISK', 'HOST', 'HOST_PACKAGE', 'PERSONALITY') and owner='TAP_OWNER' order by 3; ANALYZE the 5 tables. EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TAP_OWNER',tabname=>'A PPLICATION',me thod_opt=>'for all indexed columns'); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TAP_OWNER',tabname=>'D ISK',method_op t=>'for all indexed columns'); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TAP_OWNER',tabname=>'H OST',method_op t=>'for all indexed columns'); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TAP_OWNER',tabname=>'H OST_PACKAGE',m ethod_opt=>'for all indexed columns'); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TAP_OWNER',tabname=>'P ERSONALITY',me thod_opt=>'for all indexed columns'); 9) Check Again for Chained Rows a) truncate table chained_rows; b) SELECT 'Analyze table '||owner||'.'||segment_name||' list chained rows;' FROM DBA_SEGMENTS WHERE SEGMENT_name in ('APPLICATION', 'DISK', 'HOST',
8)
c)
'HOST_PACKAGE', 'PERSONALITY') and owner='TAP_OWNER'; select table_name,count(*) from chained_rows group by table_name;
10) Take a count of the tables after the reorganization DECLARE cursor c1 is select owner,table_name from dba_tables where owner='TAP_OWNER' AND table_name in ('APPLICATION', 'DISK', 'HOST', 'HOST_PACKAGE', 'PERSONALITY') order by 1,2; tblname varchar2(50); cnt number; stmt varchar2(500); ownr varchar2(25); tbl varchar2(50); begin stmt := 'truncate table sys.table_count_aftr_reorg'; execute immediate stmt; for i in c1 loop tbl:=i.table_name; ownr:=i.owner; tblname := i.owner||'.'||i.table_name; --dbms_output.put_line(tblname); stmt := 'select count(*) from '||ownr||'.'||tbl||''; --stmt := 'select count(*) from "'||tblname||'"'; --dbms_output.put_line(stmt); execute immediate stmt into cnt; insert into sys.table_count_aftr_reorg values(i.owner,i.table_name,cnt); --dbms_output.put_line(tblname); commit; end loop; end; / 11) Check for the Difference in count select a.table_name,a.num_rows,b.num_rows from table_count_b4_reorg a,table_count_aftr_reorg b where a.table_name=b.table_name and a.owner=b.owner and a.num_rowsb.num_rows; DESIRED OUTPUT --> NOROWS RETURNED 12) Drop the temporary tablespace
select segment_name from dba_segments where tablespace_name='TAP_DATA_TEMP'; DESIRED OUTPUT --> NOROWS RETURNED drop tablespace TAP_DATA_TEMP including datafile; 13) Make a note of post reorg image Indexes set pagesize 400 linesize 120 col table_name for a20 col index_name for a30 col owner for a20 SELECT owner,index_name,table_name,status FROM DBA_indexes WHERE table_name in ('APPLICATION', 'DISK', 'HOST', 'HOST_PACKAGE', 'PERSONALITY') and owner='TAP_OWNER' order by 3; Table Sizes set pagesize 400 linesize 120 col segment_name for a35 col segment_type for a10 col owner for a20 SELECT owner,segment_name,BYTES/1024/1024 mb FROM DBA_SEGMENTS WHERE SEGMENT_name in ('APPLICATION', 'DISK', 'HOST', 'HOST_PACKAGE', 'PERSONALITY') and owner='TAP_OWNER'; Chained Rows Image truncate table chained_rows; SELECT 'Analyze table '||owner||'.'||segment_name||' list chained rows;' FROM DBA_SEGMENTS WHERE SEGMENT_name in ('APPLICATION', 'DISK', 'HOST', 'HOST_PACKAGE', 'PERSONALITY') and owner='TAP_OWNER'; select table_name,count(*) from chained_rows group by table_name; Count of objects SPOOL obj_count.lst
select count(1),owner,object_type,status from dba_Objects group by owner,object_type,status; spool off Invalid Objects col object_name for a30 col object_type for a13 col owner for a10 set pagesize 100 linesize 120 select object_type,OWNER,OBJECT_NAME,status FROM DBA_OBJECTS WHERE OWNER='TAP_OWNER' AND STATUS ='INVALID' order by 1 ; col object_name for a30 col object_type for a13 col owner for a10 set pagesize 100 linesize 120 select Trigger_name,table_name,status FROM DBA_triggers WHERE OWNER='TAP_OWNER' and status 'ENABLED' order by 1 ; Page the user btell jyao "Reorg on the tables of TAPPRD is Done. Please check App,ora_dba 800-666-2388 xtn 1-3256"
8.13.5 NJEXPPRD Oracle Server Reorganization
To goal is to reduce database fragmentation. Approach : 1 Kill all user connections 2 Take full RMAN backup, copy to different host 3 Export al schemas 4 Drop all non-system users 5 Drop all non-system tablespaces 6 Recreate all non-system users 7 Recreate all non-system tablespaces 8 Import data only 9 Import constraints and indexes 10 Run custom analyze 11 Test application Section A - Pre-Reorganization activity 1 Get confirmation from Operations that Nigtly dump has been backed up on to tape library. 2 Make additional database dump copy (for reliable backup) i. Copy the archive logs to an alternate directory/host
3
ii. Copy the rman dump to alternate directory/host (Copy nightly database dump to nydbupora1-m:/local/ghegde/NJEXPPRD_BACKUP including archive log) Extract SQL(s) for SYSADM user. /local/oracle/NJEXPPRD/admin/reorg/scripts/cr_user_sysadm.sql CREATE USER SYSADM IDENTIFIED BY VALUES 'BA3E855E93B5B9B0' DEFAULT TABLESPACE EXLARGE TEMPORARY TABLESPACE PSTEMP PROFILE DEFAULT ACCOUNT UNLOCK / GRANT PSADMIN TO SYSADM / ALTER USER SYSADM DEFAULT ROLE PSADMIN / GRANT CREATE SESSION TO SYSADM / GRANT UNLIMITED TABLESPACE TO SYSADM / Suspend regular operations via cron on host of NJEXPPRD a) take a backup of crontab crontab –l > /local/oracle/NJEXPPRD/admin/reorg/log/crontab.20040221 b) Comment out crontab on nyoraprd2 The following jobs need to be commented out in crontab: 45 * * * * /home/dba/bin/NJEXPPRD_TempCheck_n_Clean.ksh 00 07 * * 1-5 /home/dba/bin/DBSpaceReportShort.ksh -S NJEXPPRD -D NJEXPPRD 15 07 * * 1-5 /home/dba/bin/DBChainedRows.ksh NJEXPPRD 00 08 * * 1-5 /home/dba/bin/DBExtentReport.ksh -S NJEXPPRD -D NJEXPPRD 15 08 * * 1-5 /home/dba/bin/DBSegInProblem.ksh -S NJEXPPRD -D NJEXPPRD -m ora_dba@lehman.com 30 14 * * 1-5 /home/dba/bin/DBSegInProblem.ksh -S NJEXPPRD –D NJEXPPRD -m ora_dba@lehman.com 30 2 * * 6 /local/oracle/NJEXPPRD/admin/create/scripts/AnalyzePSTables_special.ks h NJEXPPRD 0,30 * * * * /home/dba/bin/OraArchiveLog2.ksh NJEXPPRD compress c) Put the autosys jobs for Backup/Analyze/Reindex/Weekend jobs on ice : DBANPORA_NYEXPPRD --> RMAN Backup job for NJEXPPRD
4
Section B - Reorganization - Estimated start time - Saturday 6 AM 1 Expense team will shutdown application servers and page DBA. 2 DBA will verify that EXPBAK (Standby for NJEXPPRD) is in sync with NJEXPPRD (10 min) Steps: i. Login to nyoraprd2(NJEXPPRD) & njoraprd1(EXPBAK) a Open both the windows ii. On primary server sqlplus "/ as sysdba"
iii. Alter system switch logfile; (Do it 6 times) iv. Simultaneously check for the sync on the Standby host njoraprd1 v. cd /local/oracle/NJEXPPRD/admin/bdump/tail –f alert_EXPBAK.log 3 DBA will a. Lock the user SYSADM on NJEXPPRD alter user SYSADM lock; Backup existing init.ora to init.ora.before.reorg cp -p /local/oracle/NJEXPPRD/admin/pfile/initNJEXPPRD.ora /local/oracle/NJEXPPRD/admin/pfile/initNJEXPPRD.ora.20040221.pre_reorg b. Please use the following init ora file cp -p /local/oracle/NJEXPPRD/admin/reorg/initNJEXPPRD.ora.forreorg_20040221 /local/oracle/NJEXPPRD/admin/pfile/initNJEXPPRD.ora NOTE: THE ABOVE INIT FILE FOR REORG CONTAINS THE BELOW INIT PARAMETER CHANGES FOR THE REORG rollback_segments = (rbs_temp) log_buffer = 10485760 log_archive_start = false db_block_buffers = 64000 shared_pool_size = 524288000 sort_area_size = 104857600 sort_area_retained_size = 52428800 c. Recycle Oracle server NJEXPPRD (10 Minutes) Steps: i. sqlplus "/as sysdba" ii. startup mount; iii. Change the database log mode to NOARCHIVELOG iv. Alter database noarchivelog; v. alter databse open; vi. select name from v$database; vii. select instance_name from v$instance; viii. select count(*) from v$session where username is not null; d. Logon to EXPBAK on host njoraprd1 and shutdown EXPBAK as below i. sqlplus "/as sysdba" ii. recover managed standby database cancel; iii. shutdown immediate; Note - EXPBAK will be down until the application is fully tested after reorganization. 4 Take a full rman backup of NJEXPPRD (2 Hours) a. login to host nyoraprd2 i. cd /home/dba/bin OraBackup.ksh NJEXPPRD > /home/dba/log/DbaNightProcs/NJEXPPRD/OraNightProcs.log.20040221.pre_reorg; ii. btell NJEXPPRD "Pre-reorg backup is done" and Please check the backup log to confirm successful completion of backup. Steps: Check to see if backup has completed successfully by reviewing the /home/dba/log/DbaNightProcs/NJEXPPRD/OraNightProcs.log_prereorg20040221
Note down the start and end sequence of backup cd /local/oracle/NJEXPPRD/backup/ NJEXPPRD:SCN::SEQ_START:??:SEQ_END:??:0 Check to see a copy of the above archive logs (start-end sequence) exist in LogAfterDump DIR. Copy 5 logs before the start and 5 logs after the end sequence number to the LogAfterDump DIR. b. Copy the Pre-Reorg backup to njdbupora0-m:/local/ghegde/NJEXPPRD_BACKUP along with the archive logs (You do not have to wait until the rcp is done, tThis copy could proceed in the background). 5 Run user provided script to capture hash counts run /local/oracle/NJEXPPRD/admin/reorg/scripts/Hash_Counts_Execute.sql from sqlplus as sysadm/sysadm. DBA will run scripts to take snapshot of SYSADM tables, row counts, object count, tablespace freespace adn tablespace fragmentation. Collect pre-reorg snapshot of the database. a. The script ―/local/oracle/NJEXPPRD/admin/reorg/scripts/scripts.sql‖ collects DB snapshot prior to the re-org -> with reference to following parameters. select * from v$instance select name from v$database select distinct username from dba_users b. Tablespaces free space report. - tbs_free_rpt.sql SQL> CONN / AS SYSDBA set pages 1000 col tablespace_name form a25 col alloc_in_mb form 999999.99 col Free_in_mb form 999999.99 col Used_in_mb form 999999.99 col Percentage_free form 99.99 spool tbsfree.txt select a.tablespace_name,a.bytes "Alloc_in_mb", a.bytes-b.bytes "Used_in_mb", b.bytes "Free_in_mb", b.bytes*100/a.bytes "Percentage_free" from ( select tablespace_name,sum(bytes/1024/1024) bytes from dba_data_files group by tablespace_name) a, ( select tablespace_name,sum(bytes/1024/1024) bytes from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name union select tablespace_name,sum(bytes)/1024/1024 "Alloc_in_mb", sum(bytes)/1024/1024 "Used_in_mb",0 "Free_in_mb", 0 "Percentage_free" from dba_data_files where tablespace_name not in (select unique tablespace_name from dba_free_space) group by tablespace_name ; spool off c. Object count for the schema – SYSADM
6
Count of total objects, object type, validity for SYSADM in PROD --> invalid.sql SQL> CONN / AS SYSDBA spool invalid.lst set pagesize 50 linesize 120 col object_name for a30 col object_type for a7 select object_name,object_type, status from dba_objects where status ='INVALID' and owner='SYSADM'; spool off Fragmentation select tablespace_name,round(bytes/1024/1024) "size of Fragments in MB" , count(*) "No of Fragments" from dba_free_space where tablespace_name in (select distinct tablespace_name from dba_segments where owner='SYSADM') group by tablespace_name,round(bytes/1024/1024); QUERY FOR FINDING THE TABLE FRAGMENTATION & WITH SIGNIFICANT SPACE GAIN.( CONSIDERING PCTFREE 10) spool tsblr_frag_bfr_reorg.lst set pagesize 300 set linesize 120 set pagesize 300 col tablespace_name for a8 col index_name for a30 col table_name for a25 SELECT TABLE_NAME, a.tablespace_name, num_rows,AVG_ROW_LEN row_len,num_rows * AVG_ROW_LEN/1024/1024 DATA_SIZE_MB, b.pct_free,b.INITIAL_EXTENT/1024/1024 INI_EXT, b.NEXT_EXTENT/1024/1024 NXT_EXT,a.Extents , SUM(a.BYTES)/1024/1024 Tbl_size_MB , ((SUM(a.BYTES)/1024/1024) -((num_rows * AVG_ROW_LEN/1024/1024) + ((num_rows * AVG_ROW_LEN/1024/1024)*10/100))) space_gain FROM DBA_segments A,dba_tables B WHERE A.OWNER='SYSADM' AND A.OWNER=B.OWNER AND TABLE_NAME=SEGMENT_NAME --and table_name='POSITION_HISTORY' and a.tablespace_name =b.tablespace_name AND SEGMENT_TYPE='TABLE' --AND TABLE_NAME IN (SELECT TABLE_NAME FROM DBA_tables -- WHERE --OWNER ='SYSADM' AND --TO_CHAR(LAST_ANALYZED,'DD-MON-YY') > '17-SEP-03'
--) GROUP BY TABLE_NAME,a.tablespace_name,num_rows, b.INITIAL_EXTENT/1024/1024, b.NEXT_EXTENT/1024/1024,b.pct_free,a.Extents, AVG_ROW_LEN,num_rows * AVG_ROW_LEN/1024/1024 having ---(((SUM(a.BYTES)/1024/1024) -(num_rows * AVG_ROW_LEN/1024/1024))/(SUM(a.BYTES)/1024/1024))*100 0.1 ORDER BY 11 DESC ; spool off Row count for all the objects for SYSADM in PROD --> rowcount_b4_reorg.sql SQL> CONN / AS SYSDBA create table sys.table_count_b4_reorg (owner varchar2(15), table_name varchar2(50), num_rows number(15)) tablespace tools; create table sys.table_count_aftr_reorg (owner varchar2(15), table_name varchar2(50), num_rows number(15)) tablespace tools; DECLARE cursor c1 is select table_name from dba_tables where owner='SYSADM'; tblname varchar2(50); cnt number; stmt varchar2(500); begin stmt := 'truncate table sys.table_count_b4_reorg'; execute immediate stmt; for i in c1 loop tblname := i.table_name; stmt := 'select count(*) from sysadm."'||tblname||'"'; execute immediate stmt into cnt; insert into sys.table_count_b4_reorg values('SYSADM',tblname,cnt); commit; end loop; end; / Count of objects for SYSADM --> obj_count.sql SPOOL obj_count.lst select count(1),owner,object_type,status from dba_Objects group by owner,object_type,status;
spool off Redo Logs col MEMBER for a60 set linesize 120 col STATUS for a15 select * from v$logfile; select * from v$log; RBS col segment_name for a15 select segment_name,status from dba_rollback_segs; Check for Invalid object col object_name for a30 col object_type for a13 col owner for a8 set pagesize 100 linesize 120 select object_type,OWNER,OBJECT_NAME,status FROM DBA_OBJECTS WHERE OWNER='FULL_CIRCLE_OWNER' AND STATUS ='INVALID' order by 1 ;
d. Try to compile and see if they get compiled and look for status: spool comp_obj.sql col object_name for a30 col object_type for a10 select object_type,OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER='FULL_CIRCLE_OWNER' AND OBJECT_TYPE IN 'PACKAGE BODY' AND STATUS ='INVALID' order by 1 ; @ comp_obj.sql spool comp_pkgbd.sql select 'alter PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;' FROM DBA_OBJECTS WHERE OWNER='FULL_CIRCLE_OWNER' AND OBJECT_TYPE IN 'PACKAGE BODY' AND STATUS ='INVALID' order by 1 ; spool off @comp_pkgbd.sql 7. Export of sysadm & PS schema (export schema script: export_schema.ksh)
The following is the content of export par file - export_NJEXPPRD.par userid=system/ buffer=512000 compress=y owner=sysadm,ps file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.01.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.02.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.03.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.04.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.05.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.06.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.07.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.08.dmp log=/local/oracle/NJEXPPRD/admin/reorg/logs/NJEXPPRD.sysadm.log filesize=2097152000 STATISTICS=NONE RECORDLENGTH=65535 a. Start the export using syntax below (8 Hours) cd /local/oracle/NJEXPPRD/admin/reorg/scripts nyoraprd2:> date > export_schema.log ; export_schema.ksh >> export_schema.log ; date >> export_schema.log & b. After successful completion of export - Please verify log c. btell ghegde "NJEXPPRD export has been completed" PLEASE UPDATE THE CHANGE TICKET AND SEND EMAIL TO dbhatt,mmandell,ghegde & ora_dba indicating export has been completed.
NOTE -- VERY IMPORTANT: IF THE EXPORT IS NOT COMPETED BEFORE 4:00 PM EST ON 02/21/2004, A DECISION WILL BE MADE BY DIWAKAR & GAJANAN TO POSTPHONE THE REORG PROCESS. 8. If we decide to continue with the work, Drop all the objects owned by user SYSADM location: /local/oracle/NJEXPPRD/admin/reorg/scripts/drop_sysadm_obj.gen Dropping Objects before Import CHECKING FOR OTHER USERS IN SYSADM'S TABLESPACES select count(*) from dba_segments where owner='SYSTEM' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='SYSADM'); select OWNER ,count(1) from dba_segments where TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='SYSADM') group by owner ; select tablespace_name from dba_tablespaces where
TABLESPACE_NAME not IN (SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='SYSADM'); DROPPING THE USERS RETURNED FROM the ABOVE CHECK Spool drp_tbls.sql select 'drop '||object_type||' '||owner||'.'||object_name ||' CASCADE CONSTRAINTS;' from DBA_OBJECTS where owner in ('SYSADM','PS') and object_type='TABLE'; SPOOL OFF @drp_tbls.sql Spool drp_obj.sql select 'drop '||object_type||' '||owner||'.'||object_name ||';' from DBA_OBJECTS where owner in ('SYSADM','PS') ; SPOOL OFF @drp_obj.sql PLEASE VERIFY WHETHER ALL THE THE OBJECTS HAVE BEEN DROPPED by using the following query: select count(*) from dba_objects where owner='SYSADM'; 20 Minutes
9.
CHANGES FOR THE REORG Create a new ROLLBACK tablespace and a big private rollback segment.
Use the script: /local/oracle/NJEXPPRD/admin/reorg/scripts/rbs_tbs_cre.sql NOTE: PLEASE CHECK THE LOG FILE /local/oracle/NJEXPPRD/admin/reorg/logs/cr_rbs_reorg_20040221.log for successful creation. spool /local/oracle/NJEXPPRD/admin/reorg/logs/cr_rbs_reorg_20040221.log set echo on CREATE TABLESPACE RBS_TEMP DATAFILE '/local/oracle/NJEXPPRD/backup/NJEXPPRD_DATA/rbs.dbf' SIZE 2000M AUTOEXTEND OFF EXTENT MANAGEMENT DICTIONARY LOGGING DEFAULT STORAGE(INITIAL 10M NEXT 10M MINEXTENTS 1 MAXEXTENTS 3000) ONLINE PERMANENT / Alter TABLESPACE RBS_TEMP
add DATAFILE '/local/oracle/NJEXPPRD/backup/NJEXPPRD_DATA/rbs1.dbf' SIZE 2000M AUTOEXTEND OFF EXTENT MANAGEMENT DICTIONARY LOGGING DEFAULT STORAGE(INITIAL 10M NEXT 10M MINEXTENTS 1 MAXEXTENTS 3000) ONLINE PERMANENT / alter TABLESPACE RBS_TEMP add DATAFILE '/local/oracle/NJEXPPRD/backup/NJEXPPRD_DATA/rbs2.dbf' SIZE 2000M AUTOEXTEND OFF EXTENT MANAGEMENT DICTIONARY LOGGING DEFAULT STORAGE(INITIAL 10M NEXT 10M MINEXTENTS 1 MAXEXTENTS 3000) ONLINE PERMANENT / CREATE ROLLBACK SEGMENT rbs_temp TABLESPACE RBS_TEMP STORAGE(INITIAL 500M NEXT 500M MINEXTENTS 4 MAXEXTENTS 300 OPTIMAL NULL) / Alter rollback segment rbs_temp online; spool off 10. Create redo logfiles of size 500 M for reorg in backup directory. Drop the logfiles -logfile group 1,2,3. SQL> SELECT group#, ARCHIVED, STATUS FROM V$LOG ; GROUP# ARC STATUS ---------- --- --------------1 NO CURRENT 2 YES INACTIVE 3 YES INACTIVE alter system switch logfile; ==> UNTIL you see as above. /local/oracle/NJEXPPRD/admin/reorg/scripts/cr_log_for_reorg.sql
alter database add logfile group 4 '/local/oracle/NJEXPPRD/backup/NJEXPPRD_log4_1.rdo' size 500m; alter database add logfile group 5 '/local/oracle/NJEXPPRD/backup/NJEXPPRD_log5_1.rdo' size 500m; alter database add logfile group 6 '/local/oracle/NJEXPPRD/backup/NJEXPPRD_log6_1.rdo' size 500m; alter database add logfile group 7 '/local/oracle/NJEXPPRD/backup/NJEXPPRD_log7_1.rdo' size 500m; alter database add logfile group 8 '/local/oracle/NJEXPPRD/backup/NJEXPPRD_log8_1.rdo' size 500m; Before dropping make sure group 1,2,3 are INACTIVE as DISPLAYED BELOW SELECT group#, ARCHIVED, STATUS FROM V$LOG ; GROUP# ARC STATUS ---------- --- ---------------1 NO INACTIVE 2 NO INACTIVE 3 NO INACTIVE 4 NO CURRENT 5 YES UNUSED 6 YES UNUSED 7 YES UNUSED 8 YES UNUSED alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; Section C - IMPORT - Estimated start time - Saturday 6 PM - 12 Hours 1. Drop the schema – 10 Minutes SYSADM and PS (Script to be created for PS which has only 2 segments) Use the script: drop_cr_sysadm.sql (Script to be created for PS which has only 2 segments) Drop and re-create all the user tablespaces. (Script HAS to be GENERATED) Turn autoextend ON for user tablespaces - (10 Minutes) run the script /local/oracle/NJEXPPRD/admin/reorg/scripts/autoextent_on.sql Import of SYSADM Schema Use the script import_sysadm_ps_schema.sh (10 Hours) cd /local/oracle/NJEXPPRD/admin/reorg/scripts nyoraprd2: cat import_sysadm_ps_schema.sh date > time_for_import.log
2. 3. 4.
imp parfile=import_sysadm_data.par date >>time_for_import.log imp parfile=import_sysadm_nodata.par date >>time_for_import.log btell NYEXPDEV,arajeev 'import of sysadm , ps schema is complete on NJEXPPRD. Please check logs .". The IMPORT par files cat import_sysadm_data.par userid=system/ fromuser=sysadm,ps touser=sysadm,ps buffer=10240000 file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.01.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.02.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.03.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.04.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.05.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.06.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.07.dmp log=/local/oracle/NJEXPPRD/backup/exp/import_sysadm_data_tables.log rows=Y commit=n indexes=N grants=N ignore=Y filesize=2097120000 recordlength=65535 FEEDBACK=10000 cat import_sysadm_nodata.par userid=system/ fromuser=sysadm,ps touser=sysadm,ps file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.01.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.02.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.03.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.04.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.05.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.06.dmp file=/local/oracle/NJEXPPRD/backup/exp/NJEXPPRD.sysadm.07.dmp rows=N indexes=Y grants=Y ignore=Y filesize=2097120000 log=/local/oracle/NJEXPPRD/backup/exp/import_sysadm_nodata1.log 5. Verification: a. Check the import logs for any error (15 minutes) Compare row counts in export and import logs (30 Minutes)
b. Compilation of invalid objects
(15 Minutes)
spool comp_obj.sql col object_name for a30 col object_type for a10 select object_type,OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER='FULL_CIRCLE_OWNER' AND OBJECT_TYPE IN 'PACKAGE BODY' AND STATUS ='INVALID' order by 1 ; @ comp_obj.sql spool comp_pkgbd.sql select 'alter PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;' FROM DBA_OBJECTS WHERE OWNER='FULL_CIRCLE_OWNER' AND OBJECT_TYPE IN 'PACKAGE BODY' AND STATUS ='INVALID' order by 1 ; spool off @comp_pkgbd.sql c. Rebuild Invalid(UNUSABLE) Indexes Online SET PAGESIZE 50 LINESIZE 120 spool invalid_indexes.sql SELECT 'ALTER INDEX CMTE.'||INDEX_NAME||' REBUILD TABLESPACE CMTE_OMS_INDEX nologging ;' FROM DBA_INDEXES WHERE OWNER='SYSADM' and status='UNUSABLE' / spool off 6. ROLLBACK ALL THE CHANGES MADE FOR REORG a. Turn OFF Autoextend on all tablespaces (10 Minutes) Use script autoextent_off.sql . b. Drop RBS_TEMP TBS created for the reorg alter rollback segment RBS_TMP offline; drop tablespace rbs_TEMP including contents; At OS level remove the file as follows as it is large file. cd /local/oracle/NJEXPPRD/backup/NJEXPPRD_DATA/rbs1.dbf cat /dev/null > rbs.dbf cat /dev/null > rbs1.dbf
cat /dev/null > rbs2.dbf rm rbs.dbf rm rbs1.dbf rm rbs2.dbf c. Drop and create redo logfiles as below. SELECT group#, ARCHIVED, STATUS FROM V$LOG ; alter system switch logfile; ==> UNTIL you see as blow. GROUP# ARC STATUS ---------- --- ---------------4 NO CURRENT 5 NO INACTIVE 6 NO INACTIVE 7 NO INACTIVE 8 NO INACTIVE d. Add the redolog files with scripts as below. alter database add logfile group 1 ('/local/oracle/NJEXPPRD/oralog1/NJEXPPRD_log1_1.rdo', '/local/oracle/NJEXPPRD/oralog2/NJEXPPRD_log1_2.rdo') size 70m; alter database add logfile group 2 ('/local/oracle/NJEXPPRD/oralog1/NJEXPPRD_log2_1.rdo', '/local/oracle/NJEXPPRD/oralog2/NJEXPPRD_log2_2.rdo') size 70m; alter database add logfile group 3 ('/local/oracle/NJEXPPRD/oralog1/NJEXPPRD_log3_1.rdo' '/local/oracle/NJEXPPRD/oralog2/NJEXPPRD_log3_2.rdo') size 70m; e. Dropping logfiles alter system switch logfile; alter system switch logfile; ==> UNTIL you see as below SELECT group#, ARCHIVED, STATUS FROM V$LOG ; GROUP# ARC STATUS ---------- --- ---------------1 NO CURRENT 2 YES UNUSED 3 YES UNUSED 4 NO INACTIVE 5 NO INACTIVE 6 NO INACTIVE 7 NO INACTIVE 8 NO INACTIVE alter alter alter alter alter database database database database database drop drop drop drop drop logfile logfile logfile logfile logfile group group group group group 4; 5; 6; 7; 8;
f.
Clean the redo files from backup directory Following should be final image. col MEMBER for a60 set linesize 120 col STATUS for a15 select * from v$logfile;
8
Generate post-reorg snapshot of the database. a. The script ―/local/oracle/NJEXPPRD/admin/reorg/scripts/scripts.sql‖ generates DB snapshot : i. select * from v$instance ii. select name from v$database iii. select distinct username from dba_users b. Tablespaces free space report. - tbs_free_rpt.sql SQL> CONN / AS SYSDBA set pages 1000 col tablespace_name form a25 col alloc_in_mb form 999999.99 col Free_in_mb form 999999.99 col Used_in_mb form 999999.99 col Percentage_free form 99.99 spool tbsfree.txt select a.tablespace_name,a.bytes "Alloc_in_mb", a.bytes-b.bytes "Used_in_mb", b.bytes "Free_in_mb", b.bytes*100/a.bytes "Percentage_free" from ( select tablespace_name,sum(bytes/1024/1024) bytes from dba_data_files group by tablespace_name) a, ( select tablespace_name,sum(bytes/1024/1024) bytes from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name union select tablespace_name,sum(bytes)/1024/1024 "Alloc_in_mb", sum(bytes)/1024/1024 "Used_in_mb",0 "Free_in_mb", 0 "Percentage_free" from dba_data_files where tablespace_name not in (select unique tablespace_name from dba_free_space) group by tablespace_name ; spool off c. Get Object count for the schema – SYSADM and PS i. Count of total objects, object type, validity for SYSADM in PROD --> invalid.sql SQL> CONN / AS SYSDBA spool invalid.lst set pagesize 50 linesize 120
col object_name for a30 col object_type for a7 select object_name,object_type, status from dba_objects where status ='INVALID' and owner='SYSADM'; spool off ii. Fragmentation select tablespace_name, round(bytes/1024/1024) "size of Fragments in MB" , count(*) "No of Fragments" from dba_free_space where tablespace_name in (select distinct tablespace_name from dba_segments where owner='SYSADM') group by tablespace_name,round(bytes/1024/1024); iii. QUERY FOR FINDING THE TABLE FRAGMENTATION & WITH SIGNIFICANT SPACE GAIN.( CONSIDERING PCTFREE 10) spool tsblr_frag_aftr_reorg.lst set pagesize 300 set linesize 120 set pagesize 300 col tablespace_name for a8 col index_name for a30 col table_name for a25 SELECT TABLE_NAME, a.tablespace_name, num_rows,AVG_ROW_LEN row_len,num_rows * AVG_ROW_LEN/1024/1024 DATA_SIZE_MB, b.pct_free,b.INITIAL_EXTENT/1024/1024 INI_EXT, b.NEXT_EXTENT/1024/1024 NXT_EXT,a.Extents , SUM(a.BYTES)/1024/1024 Tbl_size_MB , ((SUM(a.BYTES)/1024/1024) -((num_rows * AVG_ROW_LEN/1024/1024) + ((num_rows * AVG_ROW_LEN/1024/1024)*10/100))) space_gain FROM DBA_segments A,dba_tables B WHERE A.OWNER='SYSADM' AND A.OWNER=B.OWNER AND TABLE_NAME=SEGMENT_NAME --and table_name='POSITION_HISTORY' and a.tablespace_name =b.tablespace_name AND SEGMENT_TYPE='TABLE' --AND TABLE_NAME IN (SELECT TABLE_NAME FROM DBA_tables -- WHERE --OWNER ='SYSADM' AND --TO_CHAR(LAST_ANALYZED,'DD-MON-YY') > '17-SEP-03' --) GROUP BY TABLE_NAME,a.tablespace_name,num_rows, b.INITIAL_EXTENT/1024/1024, b.NEXT_EXTENT/1024/1024,b.pct_free,a.Extents, AVG_ROW_LEN,num_rows * AVG_ROW_LEN/1024/1024 having
---(((SUM(a.BYTES)/1024/1024) -(num_rows * AVG_ROW_LEN/1024/1024))/(SUM(a.BYTES)/1024/1024))*100 0.1 ORDER BY 11 DESC ; spool off iv. Count of objects for SYSADM --> obj_count.sql SPOOL obj_count_afr_reorg.lst select count(1),owner,object_type,status from dba_Objects group by owner,object_type,status; spool off v. Redo Logs col MEMBER for a60 set linesize 120 col STATUS for a15 select * from v$logfile; select * from v$log; vi. RBS col segment_name for a15 select segment_name,status from dba_rollback_segs;
vii. Check for Invalid objects col object_name for a30 col object_type for a13 col owner for a8 set pagesize 100 linesize 120 select object_type,OWNER,OBJECT_NAME,status FROM DBA_OBJECTS WHERE OWNER in ('SYSADM', 'PS') AND STATUS ='INVALID' order by 1 ; d. Try to compile and see if they get compiled and look for status. spool comp_obj.sql col object_name for a30 col object_type for a10 select object_type,OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER in ('SYSADM', 'PS') AND OBJECT_TYPE IN 'PACKAGE BODY' AND STATUS ='INVALID' order by 1 ; @ comp_obj.sql
spool comp_pkgbd.sql select 'alter PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;' FROM DBA_OBJECTS WHERE OWNER='FULL_CIRCLE_OWNER' AND OBJECT_TYPE IN 'PACKAGE BODY' AND STATUS ='INVALID' order by 1 ; spool off @comp_pkgbd.sql 9 DBA will run scripts to check for the count match Row count for all the objects for SYSADM in PROD --> rowcount_aftr_reorg.sql SQL> CONN / AS SYSDBA create table sys.table_count_aftr_reorg (owner varchar2(15), table_name varchar2(50), num_rows number(15)) tablespace tools; DECLARE cursor c1 is select table_name from dba_tables where owner='SYSADM'; tblname varchar2(50); cnt number; stmt varchar2(500); begin stmt := 'truncate table sys.table_count_aftr_reorg'; execute immediate stmt; for i in c1 loop tblname := i.table_name; stmt := 'select count(*) from sysadm."'||tblname||'"'; execute immediate stmt into cnt; insert into sys.table_count_aftr_reorg values('SYSADM',tblname,cnt); commit; end loop; end; / select count(table_name) from dba_tables where owner='SYSADM'; select count(table_name) from table_count_b4_reorg; select count(table_name) from table_count_aftr_reorg; select a.table_name,a.num_rows,b.num_rows from table_count_b4_reorg a,table_count_aftr_reorg b where a.table_name=b.table_name and a.owner=b.owner and a.num_rowsb.num_rows;
DESIRED OUTPUT --> NOROWS RETURNED select count(*) from table_count_b4_reorg a,table_count_aftr_reorg b where a.table_name=b.table_name and a.owner=b.owner and a.num_rows=b.num_rows; DESIRED OUTPUT --> COUNT EQUAL TO NUMBER OF TABLES IN SYSADM. 10 11 DBA will RUN the script provided by Diwakar Bhatt to verify the integrity of DATA. cd /local/oracle/NJEXPPRD/admin/reorg/scripts/ sqlplus sysadm/sysadm @Hash_Counts_Execute.sql Open database for application testing a. Recycle and Switch to archive log mode: 10 minutes i. COPY ORIGINAL init file back . cp /local/oracle/NJEXPPRD/admin/reorg/initNJEXPPRD.ora.beforereorg_20040221 /local/oracle/NJEXPPRD/admin/pfile/initNJEXPPRD.ora ii. Recycle the INSTANCE and put it on Archive log. startup mount Alter database archivelog; alter database open; archive log start b. Startup Listener c. Check for DB connectivity d. Inform/page Application group (dbhatt,mmandell ) – NJEXPPRD MILESTONE DBA will page Gajanan Hegde, Diwakar Bhatt - NJEXPPRD reorganization is now complete btell ghegde,dbhatt NJEXPPRD reorganization is now complete - Please test application. Note – Database analyze will run in parallel - application may run slow until analyze is complete. 12 Uncomment crontab a. The following jobs need to be uncommented out in crontab: 45 * * * * /home/dba/bin/NJEXPPRD_TempCheck_n_Clean.ksh 00 07 * * 1-5 /home/dba/bin/DBSpaceReportShort.ksh -S NJEXPPRD -D NJEXPPRD 15 07 * * 1-5 /home/dba/bin/DBChainedRows.ksh NJEXPPRD 00 08 * * 1-5 /home/dba/bin/DBExtentReport.ksh -S NJEXPPRD -D NJEXPPRD 15 08 * * 1-5 /home/dba/bin/DBSegInProblem.ksh -S NJEXPPRD -D NJEXPPRD -m ora_dba@lehman.com 30 14 * * 1-5 /home/dba/bin/DBSegInProblem.ksh -S NJEXPPRD -D NJEXPPRD -m ora_dba@lehman.com 30 2 * * 6 /local/oracle/NJEXPPRD/admin/create/scripts/AnalyzePSTables_special.ks h NJEXPPRD 0,30 * * * * /home/dba/bin/OraArchiveLog2.ksh NJEXPPRD compress
b. DeICE autosys job - DBANPORA_NJEXPPRD 13 14 15 16 Analyze SYSADM using the script - (4 Hours) /local/oracle/NJEXPPRD/admin/create/scripts/AnalyzePSTables_special.ksh NJEXPPRD btell dbhatt,ghegde NJEXPPRD analyze job has been completed Take a full rman Backup of NJEXPPRD DO NOT SYNC THE STANDBY UNTIL Gajanan/application group gives clearance.
CLOSE REMEDY ticket Appendix List of Scripts for the reorganization NJEXPPRD@nyoraprd2:/local/oracle/NJEXPPRD/admin/reorg/scripts> ls -l total 210 -rw-r--r-- 1 oracle dba 1586 Feb 21 00:06 Hash_Counts_Execute.sql -rw-r--r-- 1 oracle dba 11859 Feb 10 12:59 autoextent_off.sql -rw-r--r-- 1 oracle dba 19095 Feb 20 14:55 autoextent_on.sql -rw-r--r-- 1 oracle dba 11012 Feb 10 12:37 coalesce_tablespace.sql -rw-r--r-- 1 oracle dba 606 Feb 20 14:36 cr_log_for_reorg.sql -rw-r--r-- 1 oracle dba 593 Feb 20 14:46 cr_log_post_reorg.sql -rw-r--r-- 1 oracle dba 36149 Feb 21 01:28 cr_tbs_NJEXPPRD.sql -rw-r--r-- 1 oracle dba 254 Feb 20 10:32 cr_user_sysadm.sql -rw-r--r-- 1 oracle dba 319 Feb 7 03:38 cre_sysadm_user.sql -rw-r--r-- 1 oracle dba 329 Feb 10 12:50 drop_cr_sysadm.sql -rw-r--r-- 1 oracle dba 317 Feb 20 13:19 drop_sysadm_obj.gen -rw-r--r-- 1 oracle dba 621 Feb 21 00:37 export_NJEXPPRD.par -rwx--x--x 1 oracle dba 685 Feb 21 00:34 export_schema.ksh -rw-r--r-- 1 oracle dba 668 Feb 21 00:38 import_sysadm_data.par -rw-r--r-- 1 oracle dba 611 Feb 21 00:38 import_sysadm_nodata.par -rwxr-xr-x 1 oracle dba 237 Feb 21 00:39 import_sysadm_ps_schema.sh -rw-r--r-- 1 oracle dba 205 Feb 6 23:48 invalid.sql -rw-r--r-- 1 oracle dba 191 Feb 6 23:56 invalid_general.sql -rw-r--r-- 1 oracle dba 97 Feb 6 23:54 obj_count.sql -rw-r--r-- 1 oracle dba 1285 Feb 20 14:31 rbs_tbs_cre.sql -rw-r--r-- 1 oracle dba 366 Feb 6 23:37 rbs_tbs_cre1.sql -rwxrwxr-x 1 oracle dba 414 Feb 21 00:01 rowcount_aftr_reorg.sql -rwxrwxr-x 1 oracle dba 410 Feb 21 00:02 rowcount_b4_reorg.sql -rw-r--r-- 1 oracle dba 487 Feb 6 16:40 run_cnt.sql -rw-r--r-- 1 oracle dba 1317 Feb 8 18:27 scripts.sql -rw-r--r-- 1 oracle dba 199 Feb 20 11:37 sysadm_obj_cnt.sql -rw-r--r-- 1 oracle dba 1066 Feb 10 13:01 tbs_free_rpt.sql
8.13.6 Reorganization For NJTTPRD1
DELETION DONE BY USER RAW_PRICE_FX_SPOT_EBS_FIN 1600 MB LPF_RAW1
RAW_PRICE_FX_SPOT_EBS_TRI RAW_PRICE_FX_SPOT_RD2_TRI NORM_PRICE_FX ACTIVITIES
1600 MB LPF_RAW1 1600 MB LPF_RAW1 2500 MB LPF_DATA1
TAKE EXP BACKUP OF THE TABLES CREATING TEMPORARY TABLESPACE FOR MOVING OBJECTS CREATE TABLESPACE LPF_TEMP DATAFILE '/local/oracle/NJTTPRD1/backup/reorg/lpf_temp1.dbf' size 25000 m, '/local/oracle/NJTTPRD1/backup/reorg/lpf_temp2.dbf' size 25000 m extent management local uniform size 100m / FOR FINDING INFORMATION ABOUT TABLES set pagesize 50 set linesize 120 col tablespace_name for a16 col index_name for a30 col table_name for a30 SELECT table_name ,a.tablespace_name, num_rows, SUM(BYTES)/1024/1024 Tbl_size_MB , MIN(BYTES)/1024/1024 MIN_ext_MB, MAX(BYTES)/1024/1024 MAX_ext_MB FROM DBA_EXTENTS A,dba_tables B WHERE A.OWNER='LPF' AND A.OWNER=B.OWNER AND TABLE_NAME=SEGMENT_NAME and a.tablespace_name =b.tablespace_name AND SEGMENT_TYPE='TABLE' AND TABLE_NAME IN ( 'RAW_PRICE_FX_SPOT_EBS_FIN', 'RAW_PRICE_FX_SPOT_EBS_TRI', 'RAW_PRICE_FX_SPOT_RD2_TRI', 'NORM_PRICE_FX') GROUP BY TABLE_NAME,a.tablespace_name,num_rows ; BEFORE REORG TABLE_NAME TABLESPACE_NA NUM_ROWS TBL_SIZE_MB MIN_EXT_MB MAX_EXT_MB ---------------------------- ------------- ---------- ----------- ---------- ---------NORM_PRICE_FX LPF_DATA1 74854485 4490 10 10 RAW_PRICE_FX_SPOT_EBS_FIN LPF_RAW1 9158927 9600 100 100 RAW_PRICE_FX_SPOT_EBS_TRI LPF_RAW1 9304667 9500 100 100 RAW_PRICE_FX_SPOT_RD2_TRI LPF_RAW1 9731596 9500 100 100 FOR FINDING INFORMATION ABOUT INDEXES set pagesize 50
set linesize 120 col tablespace_name for a16 col index_name for a30 col table_name for a30 SELECT index_name ,B.TABLE_NAME,a.tablespace_name, SUM(BYTES)/1024/1024 Tbl_size_MB , MIN(BYTES)/1024/1024 MIN_ext_MB, MAX(BYTES)/1024/1024 MAX_ext_MB FROM DBA_EXTENTS A,dba_indexes B WHERE A.OWNER='LPF' AND A.OWNER=B.OWNER AND SEGMENT_NAME =INDEX_NAME AND INDEX_NAME IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME IN ( 'RAW_PRICE_FX_SPOT_EBS_FIN', 'RAW_PRICE_FX_SPOT_EBS_TRI', 'RAW_PRICE_FX_SPOT_RD2_TRI', 'NORM_PRICE_FX')) and a.tablespace_name =b.tablespace_name AND SEGMENT_TYPE='INDEX' GROUP BY INDEX_NAME,a.tablespace_name,B.TABLE_NAME ; BEFORE REORG INDEX_NAME TABLE_NAME TABLESPACE_NAME TBL_SIZE_MB MIN_EXT_MB MAX_EXT_MB ------------------------------ ------------------------------ ---------------- ----------- ---------- ---------NORM_PRICE_FX_IDX1 NORM_PRICE_FX LPF_INDEX 1394 2 2 RAW_PRICE_FX_SPOT_EBS_FIN_NDX RAW_PRICE_FX_SPOT_EBS_FIN LPF_INDEX_LARGE 290 10 10 RAW_PRICE_FX_SPOT_EBS_TRI_INDX RAW_PRICE_FX_SPOT_EBS_TRI LPF_INDEX_LARGE 310 10 10 RAW_PRICE_FX_SPOT_RD2_TRI_INDX RAW_PRICE_FX_SPOT_RD2_TRI LPF_INDEX_LARGE 300 10 10 INDEX_NAME TABLE_NAME TABLESPACE_NAME TBL_SIZE_MB MIN_EXT_MB MAX_EXT_MB ------------------------------ ------------------------------ ---------------- ----------- ---------- ---------NORM_PRICE_FX_IDX1 NORM_PRICE_FX LPF_INDEX 768 2 2 RAW_PRICE_FX_SPOT_EBS_FIN_NDX RAW_PRICE_FX_SPOT_EBS_FIN LPF_INDEX_LARGE 220 10 10 RAW_PRICE_FX_SPOT_EBS_TRI_INDX RAW_PRICE_FX_SPOT_EBS_TRI LPF_INDEX_LARGE 220 10 10 RAW_PRICE_FX_SPOT_RD2_TRI_INDX RAW_PRICE_FX_SPOT_RD2_TRI LPF_INDEX_LARGE 220 10 10 FREE SPACE select a.tablespace_name,sum(a.bytes/1024/1024) Tot_mb, sum(free_mb) Free_mb, round(((sum(free_mb)/sum(a.bytes/1024/1024))*100),2)Percent_free from
dba_data_files a,(select tablespace_name, file_id,sum(bytes/1024/1024) free_mb from dba_free_space group by tablespace_name, file_id) b where a.tablespace_name=b.tablespace_name(+) and a.file_id=b.file_id(+) and a.tablespace_name IN ('LPF_DATA1', 'LPF_RAW1', 'LPF_INDEX', 'LPF_INDEX_LARGE') group by a.tablespace_name order by 1; select a.tablespace_name,sum(a.bytes/1024/1024) Tot_mb, sum(free_mb) Free_mb, round(((sum(free_mb)/sum(a.bytes/1024/1024))*100),2) PERCENT_FREE, (100 round(((sum(free_mb)/sum(a.bytes/1024/1024))*100),2)) PERCENT_USED from dba_data_files a,(select tablespace_name, file_id,sum(bytes/1024/1024) free_mb from dba_free_space group by tablespace_name, file_id) b where a.tablespace_name=b.tablespace_name(+) and a.file_id=b.file_id(+) group by a.tablespace_name having (100- round(((sum(free_mb)/sum(a.bytes/1024/1024))*100),2)) >90 order by 1 / select tablespace_name,max(bytes/1024/1024) Max_mb, sum(bytes/1024/1024) free_mb from dba_free_space where tablespace_name IN ('LPF_TEMP' ) group by tablespace_name order by 1; select tablespace_name,sum(bytes/1024/1024) TOT_mb from dba_data_files where tablespace_name IN ('LPF_DATA1', 'LPF_RAW1', 'LPF_INDEX', 'LPF_INDEX_LARGE' ) group by tablespace_name order by 1; BEFORE REORG TABLESPACE_NAME TOT_MB FREE_MB PERCENT_FREE ---------------- ---------- ---------- -----------LPF_DATA1 36000 13180 36.61
LPF_INDEX 4000 LPF_INDEX_LARGE 30000 LPF_RAW1 203478 AFTER REORG
2576 8150 11200
64.40 27.17 5.50
TABLESPACE_NAME TOT_MB FREE_MB PERCENT_FREE ---------------- ---------- ---------- -----------LPF_DATA1 36000 15430 42.86 LPF_INDEX 4000 3202 80.05 LPF_INDEX_LARGE 30000 8390 27.97 LPF_RAW1 203478 19800 9.73 FREE SPACE - DATAFILE WISE select tablespace_name,file_id ,max(bytes/1024/1024) sum(bytes/1024/1024) free_mb from dba_free_space where tablespace_name IN ('LPF_DATA1', 'LPF_RAW1', 'LPF_INDEX', 'LPF_INDEX_LARGE') group by tablespace_name,file_id order by 1; TABLESPACE_NAME FILE_ID MAX_MB FREE_MB ---------------- ---------- ---------- ---------LPF_DATA1 23 11010 11010 LPF_DATA1 28 2170 2170 LPF_INDEX 8 1300 1300 LPF_INDEX 20 1276 1276 LPF_RAW1 4 100 500 LPF_RAW1 9 200 1000 LPF_RAW1 10 300 1200 LPF_RAW1 14 1900 1900 LPF_RAW1 16 600 1500 LPF_RAW1 19 100 700 LPF_RAW1 21 200 600 LPF_RAW1 22 100 400 LPF_RAW1 24 100 1000 LPF_RAW1 25 100 600 LPF_RAW1 26 100 400 LPF_RAW1 27 100 400 LPF_RAW1 29 100 300 LPF_RAW1 30 100 600 LPF_RAW1 31 100 100 Moving to Temporary tablespace LPF_TEMP ALTER TABLE LPF.NORM_PRICE_FX MOVE TABLESPACE LPF_TEMP pctfree 2 pctused 95; ALTER TABLE LPF.RAW_PRICE_FX_SPOT_EBS_FIN MOVE TABLESPACE LPF_TEMP pctfree 2 pctused 95; Max_mb,
ALTER TABLE LPF.RAW_PRICE_FX_SPOT_EBS_TRI LPF_TEMP pctfree 2 pctused 95; ALTER TABLE LPF.RAW_PRICE_FX_SPOT_RD2_TRI LPF_TEMP pctfree 2 pctused 95;
MOVE MOVE
TABLESPACE TABLESPACE
ALTER INDEX LPF.NORM_PRICE_FX_IDX1 REBUILD TABLESPACE LPF_TEMP PCTFREE 2; ALTER INDEX LPF.RAW_PRICE_FX_SPOT_EBS_FIN_NDX REBUILD TABLESPACE LPF_TEMP PCTFREE 2; ALTER INDEX LPF.RAW_PRICE_FX_SPOT_EBS_TRI_INDX REBUILD TABLESPACE LPF_TEMP PCTFREE 2; ALTER INDEX LPF.RAW_PRICE_FX_SPOT_RD2_TRI_INDX REBUILD TABLESPACE LPF_TEMP PCTFREE 2; Moving FROM Temporary tablespace LPF_TEMP TO RESPECTIVE TABLESPACES ALTER TABLE LPF.NORM_PRICE_FX MOVE TABLESPACE pctused 95; ALTER TABLE LPF.RAW_PRICE_FX_SPOT_EBS_FIN LPF_RAW1 pctfree 2 pctused 95; ALTER TABLE LPF.RAW_PRICE_FX_SPOT_EBS_TRI LPF_RAW1 pctfree 2 pctused 95; ALTER TABLE LPF.RAW_PRICE_FX_SPOT_RD2_TRI LPF_RAW1 pctfree 2 pctused 95; LPF_DATA1 pctfree 2 MOVE MOVE MOVE TABLESPACE TABLESPACE TABLESPACE
ALTER INDEX LPF.NORM_PRICE_FX_IDX1 REBUILD TABLESPACE LPF_INDEX PCTFREE 2; ALTER INDEX LPF.RAW_PRICE_FX_SPOT_EBS_FIN_NDX REBUILD TABLESPACE LPF_INDEX_LARGE PCTFREE 2; ALTER INDEX LPF.RAW_PRICE_FX_SPOT_EBS_TRI_INDX REBUILD TABLESPACE LPF_INDEX_LARGE PCTFREE 2; ALTER INDEX LPF.RAW_PRICE_FX_SPOT_RD2_TRI_INDX REBUILD TABLESPACE LPF_INDEX_LARGE PCTFREE 2; DROP THE LPF_TEMP TABLESPACE sql> DROP TABLESPACE LPF_TEMP ; The Files are large so remove them by following from file system otherwise the inodes will not be freed up. 1) $ cat /dev/null > file_name /* This will zero the file & Free up the i-nodes */ e.g. $ cat /dev/null > /local/oracle/NJTTPRD1/backup/reorg/lpf_temp1.dbf 2) $ rm /local/oracle/NJTTPRD1/backup/reorg/lpf_temp1.dbf ANALYZE TABLE LPF.NORM_PRICE_FX ESTIMATE STATISTICS SAMPLE 15 PERCENT; ANALYZE TABLE LPF.RAW_PRICE_FX_SPOT_EBS_FIN ESTIMATE STATISTICS SAMPLE 15 PERCENT; ANALYZE TABLE LPF.RAW_PRICE_FX_SPOT_EBS_TRI ESTIMATE STATISTICS SAMPLE 15 PERCENT;
ANALYZE TABLE LPF.RAW_PRICE_FX_SPOT_RD2_TRI ESTIMATE STATISTICS SAMPLE 15 PERCENT;
9
Database Administration Tasks
9.1 Procedure to Refresh a Server
The Server Refresh is taken up on the request by the users. The users request a refresh of a particular Development/Staging Server from the respective Staging Server. For e.g. a request that we received for the refresh of CAMEOQA from the related production environment CAMEO. The Steps that were followed are: 1 Disable the monitoring for the Server with the following Unix level command g2disable –so -ti i.e. g2disable –so CAMEOQA –ti 1200 Comment all the archive cleaning jobs on the target Host which are set up for cleanup for this Server. The data file path for the restore should be made similar to the Source Server. This can be done by creation of Soft links to create a virtual path. e.g. the path on Target CAMEOQA - /local/oracle/CAMEOQA should be made similar to the source on CAMEO - /local/oracle/CAMEO by creation of a link on the target CAMEOQA /local/oracle as follows ln –s /local/oracle/CAMEOQA CAMEO Check the presence of the parameter file init.ora (e.g. initCAMEOQA.ora) in $ORACLE_HOME/dbs. In Oracle 8i Servers that are set up the parameter file is located on the directory local to the Server and is linked from $ORACLE_HOME/dbs directory, whereas for Oracle 9i Servers it is centrally managed in $ORACLE_HOME/dbs directory. Edit the parameter file of the target server (initCAMEOQA.ora) to change name of the Server to the name of the Source e.g. CAMEO. Before changing create a backup file for parameter file . db_name=CAMEOQA -- to be changed to CAMEO instance_name=CAMEOQA-- to be changed to CAMEO service_names=CAMEOQA -- to be changed to CAMEO i.e. should be changed to db_name=CAMEO Instance_name=CAMEO service_names=CAMEO 6 Perform the steps 2 to step 11 mentioned in the process for full database recovery in the previous section Recovery. Completing the step would have brought the target server (CAMEOQA) to the state of the source server (CAMEO) .
2 3
4
5
7
Edit the parameter file of the target server (initCAMEOQA.ora) to change name of the Server back to its original one or copy the backup file to the original name initCAMEOQA.ora. The parameter should now be db_name=CAMEOQA instance_name=CAMEOQA service_names=CAMEOQA Modify the trace file generated in directory mentioned in user_dump_dest , i.e. /local/oracle/CAMEOQA/admin/udump to change the name of the Server to the Original name (Target). Change the Script to reflect changes as highlighted in Bold in the following line. CREATE CONTROLFILE REUSE SET DATABASE "CAMEOQA" RESETLOGS ARCHIVELOG
8
9 10 11 12 13
Run the modified Script from SVRMGR> OR SQL>. Shutdown the Server normal and Startup. Query the necessary data dictionary views to ascertain the database name is changed and that the state of the Server is identical to the Source and is Operational. Query for the SYS and SYSTEM password using ―getsap‖ utility and update the SYS and SYSTEM passwords appropriately for the refreshed instance. Notify the Users for completion of the Refresh and the State in time to which it is Refreshed.
9.1.1
Post Refresh Tasks
The DB CAMEOQA residing on the host NYCAMEOQA has been refreshed from the production DB CAMEO.Following has been checked on the target DB CAMEOQA after refresh. Target or the refreshed DB info: db_name string CAMEOQA instance_name string CAMEOQA service_names string CAMEOQA Check the following Is DB in READ/WRITE mode ? Are all tablespaces online ? Distinct Status of tablespaces Distinct Status of datafiles Distinct Status of all rollback segments Are you able to do sort operations ? Yes. Yes Online Online, System Online Yes, issued a query with order by. Yes, 1148 in CAMEOQA, same as in prod. Yes, 43 in
Has the check been made against the no of users? Has the check been made against the no: of datafiles?
CAMEOQA,same as in prod. Password of Sys and System before and after refresh of CAMEOQA Same as the one got from getsap CAMEOQA Is the Listener running? YES Is the Client connectivity to the DB checked/how?Yes, by sqlplus from another host Any invalid objects still existing owned by non-system users? NONE Is the DB CAMEOQA in Archive log mode? Yes Is the log switch happening? Yes Are the relevant archive-oriented cronjobs enabled? Yes
Prior to the refresh process, g2disable was issued on nycameoqa host (g2disable -so CAMEOQA -ti 1200). Output: success. DISABLE_ID=A19206 Id Source Fault Action From To Disabled_By Enabled_By Ref_id Note A19206 CAMEOQA all.all.all all 04/18/03 23:16 04/19/03 19:16 oracle Prior to the refresh process , following 3 cronjobs pertaining to archive logs was commented out. # Remove all but last 5 arch log files #5,15,25,35,45,55 * * * * /home/dba/bin/ArchCleanUp.ksh CAMEO 5 > /dev/null 2 >&1 #0,10,20,30,40,50 * * * * /home/dba/bin/OraArchiveLog3.ksh CAMEOQA >> / home/dba/log/syslog_CAMEOQA 2>&1 #0,45 9,14,16,18,20,23 * * 2-5 find /local/oracle/CAMEOQA/arch -name 'CAMEO.log. *' -mtime +0 -a -exec rm -f {} \; > /dev/null 2>&1 This should be put back in place, since the refresh is complete. SVRMGR> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /local/oracle/CAMEO/arch Oldest online log sequence 2 Next log sequence to archive 9 Current log sequence 9 SVRMGR> !hostname nycameoqa SVRMGR> select name from v$database; NAME --------CAMEOQA 1 row selected. SVRMGR> !date Sat Apr 19 16:55:22 EDT 2003
9.2
1
To create otrace for OEM Diagnostic Pack : Trace Data Viewer
Set up init.ora and recycle the instance oracle_trace_collection_path = $ORACLE_HOME/otrace/admin/cdf oracle_trace_collection_size = 5242880 oracle_trace_enable = TRUE oracle_trace_facility_name = oracled oracle_trace_facility_path = $ORACLE_HOME/otrace/admin/fdf create user otrace schema create user otrace identified by otrace default tablespace oem_repository temporary tablespace temp; grant connect, resource to otrace; grant select any table to otrace;
2
Create a file for trace format: $ORACLE_HOME/dbs/oratrc.ctl
Example: ======== col_name= test1 dat_file= test1.dat cdf_file= test1.cdf fdf_file= oracled.fdf regid= 1 192216243 0 0 5 ETGDEV1 3 4 5 6 7 8 9 10 11 Ensure that the file oracled.fdf exists in $ORACLE_HOME/otrace/admin/fdf. Ensure that there are files collect.dat, facility.dat, regid.dat in $ORACLE_HOME/otrace/admin Ensure that there is not a file in $ORACLE_HOME/otrace/admin/cdf which matches any of the filenames in $ORACLE_HOME/dbs/oratrc.ctl. Ensure that there are files collect.dat, facility.dat, regid.dat in $ORACLE_HOME/otrace/admin. These files should be seen after step one is done. Start Oracle Tracing: otrccol start For example: otrccol start 1234 $ORACLE_HOME/dbs/oratrc.ctl Let the application run. Once we want to analyze trace, we can stop tracing: otrccol stop 1234 $ORACLE_HOME/dbs/oratrc.ctl Copy the files test1.cdf and test1.dat from $ORACLE_HOME/otrace/admin/cdf to your working directory. Import data to this schema : otrcfmt -f -c500 test1.cdf otrace/otrace@$ORACLE_SID
12
Check if the below tables exist in otrace schema: EPC_COLLECTION EPC_FACILITY_REGISTRATION Use Diagnostic Pack: Trace Data Viewer as otrace/otrace to see the traces.
13
9.3
HPOV Monitor - Resource Usage
To check a hosts resource usage - historical etc .. URL ... http://njgisrpt002/HPOV_reports/OVPM.htm
10
Scripts
10.1 System State Dumps
SERVER LLPROD or NYLLPROD is hung When the Servers LLPROD OR NYLLPROD are hung please issue the following scripts On both njecomdb1-m and nyecomdb1-m: /home/dba/bin/system_state_dump.ksh /home/dba/bin/system_state_dump_alternative.ksh NOTE: Run these scripts when server is hung and you can not get SQLs return from the server. These are not to be used when the system is in normal/healthy state.
10.2 10.3
Database Startup Scripts Sybase and Oracle Database Server Shutdown Scripts
To prevent outages due to unintended production server, shutdown scripts ShutdownSybase.ksh and ShutdownOracle.ksh have been deployed in /home/dba/bin on all servers. Going forward, all server shutdowns must be initiated by these scripts. In other words, usage of traditional command line utilities such as isql or sqlplus/svrmgrl for data server shutdown should be avoided. A brief description of the script functionality has been appended below. If you have any questions, please call Gajanan or Dave Richards. Database Shutdown Scripts for Sybase and Oracle Purpose To prevent accidental shutdown of production ( or wrong) server. Scripts Location : Host: /home/dba/bin/ShutdownOracle.ksh Host : /home/dba/bin/ShutdownSybase.ksh. Usage :
1 2 3
Determine host for server Login to the host as (Sybase or Oracle) Execute following (Sybase or Oracle)
Logic : 1 If server is not in datacompass, exit. 2 If production server, prompt warning message and wait for explicit acknowledgement for shutdown. 3 If confirmed by DBA, display message '... press ctrl-c to abort', a last chance to abort message and sleep for 15 second. 4 If ctrl-c is not aborted, btell ora_dba/fi_dba server is being shutdown ... a. For sybase, - checkpoint each database - shutdown server b. For Oracle, - use /home/dba/bin/oractl and shutdown listener - and, shutdown oracle 5. Write error log.
10.4
Determine the Process that consumes most CPU
Script (for NYESERV) nyecomdb3-m for processes that take 40% of CPU or more. Login as oracle on nyecomdb3-m: 1 2 3 4 5 6 Issue top Get the process id number at unix level that consumes most CPU Login as system@NYESERV @/home/dba/sql/current_user_activity_in_db.sql View /home/dba/log/SID_current_user_activity.log View /home/dba/log/SID_current_user_activity.log and search for the OS process number from step 2.
10.5
Set Time SQL> set time on 17:09:39 SQL> set timing on LEX spool q_bad.out
Turn Auto Tracing on for SQL statements
Turning Auto Trace on for SQL statements would give the explain plan along with SQL output.
SET AUTOTRACE ON SET TIMING ON SELECT recipientcompany, recipientname, requestername, onbehalfname, miscstring6 FROM lboresearchsent_target WHERE docid = '508969' AND statuscode IN (1,9) ORDER BY recipientcompany, recipientname, requestername, onbehalfname,miscstring6 ; SET AUTOTRACE OFF spool off quit ;
10.6
NJRAPPRD index Rebuild Script
The following attached file contains the script to rebuild the index
11
Backup and Recovery
11.1 Procedure to take the Backup of the Database
Database Backups can be taken in two ways 1. HOT BACKUPS 2. COLD BACKUPS Hot backups are taken when the database is up and running and also the Database should be in archivelog mode to take the hot backup. For very critical production databases the hot backup will be scheduled daily . In Lehman Brothers the production Database backups are scheduled daily through Autosys TOOL
11.2 How to restore the Backup of the Database on Development/Staging
These are the steps to Refresh DB2 from DB1 On DB2 : - g2disable -so -ti Stop /shutdown the instance DB2 The Rman dump of the source DB1 has to be RCP‟d to the target host machine under the backup directory of the database DB2 RCP the following from DB1 backup directory to the DB2 backup directory
rcp *.0 rcp *.ctl rcp the logfiles as explained below Verify the log sequence generated in the Source DB backup directory during the RMAN backup Example : LLPROD:SCN:1.1553E+10:SEQ_START:59580:SEQ_END:59596:0 Here the archive log sequence has started at 59580 and ended at 59596 , so DBA needs to copy only the 16 logs for the refresh On DB2 : Make a copy of the original init.ora file as a precautionary measure and name the backup file with the date ex : init.ora_20042804 Copy the control files copied from the Source database DB1 to the locations as mentioned in the init.ora file on the target DB2 On the original init.ora file change the value‟s of the following parmeters db_name = db_name1 instance_name=primary _sid(db_name1) service_names=primary_sid(db_name1) log_archive_format=primary_sid.log%T%S Copy the restore script rman and the shell script) from any of the servers Make sure that Oracle_SID and ORACLE_HOME is set correctly in the script on DB2 Connect to the Database DB2 -- Startup mount Run the restore script ( The Server name (instance name) has to be changed to accommodate the name of the appropriate server that is to be restored. Example : restore_R2RSYS.ksh and restore_R2RSYS.rman #!/bin/ksh R2RSYS.ksh echo `date` > rman.R2RSYS.out cat restore_R2RSYS.rman |$ORACLE_HOME/bin/rman target / nocatalog >> rman.R2RSYS.out echo `date` >> rman.R2RSYS.out btell "R2RSYS restore done on R2RSYS" R2RSYS.rman connect target ; run { allocate allocate allocate allocate channel aux1 type disk ; channel aux2 type disk ; channel aux3 type disk ; channel aux4 type disk ;
allocate channel aux5 type disk ; allocate channel aux6 type disk ; restore (database); switch datafile all; release channel aux1 release channel aux2 release channel aux3 release channel aux4 release channel aux5 release channel aux6 } ; ; ; ; ; ;
After the restore is done point in time recovery is to be done from the command prompt SVRMGRL> recover database using backup controlfile until cancel; At the prompt type Auto to invoke an automatic recovery Once all the archive logs are applied open the database using “RESETLOGS OPTION)” SVRMGRL> Alter database open resetlogs; Check whether the database is in READ/WRITE mode and the tablespaces and the datafiles are online. Take a trace dump of the control file by using the command. SVRMGRL> Alter database backup controlfile to trace; Shutdown the Database Copy the trace file and modify the same ,change the name of the Server(Database) to the origina; (Target) ,change the script to reflect changes as highlighted in Bold CREATE CONTROLFILE REUSE SET DATABASE “DB2” RESETLOGS ARCHIVELOG Change the following in the init.ora parameter back instance_name=DB2 , Service_names=db2 Startup nomount; Run the trace file Alter database open resetlogs; After verifying the database notify the users that the refresh is done
11.3 Procedure to take the Export Backup of the Database
Export backup is one of the means of taking Database /Schema backup Export backup will be taken as per the requirement ie daily/weekly etc
For running export it requires a parameter file called the par file where we specify all the parameters that is required to take an export backup successfully Here in export par file we can assign the data dump to multiple dump files and restrict the size of the dump files to be within 2 gb , as some of the machines/hosts may not support files bigger than 2 GB. Example of the parameter file :userid=system/liberty83 file= x1_exp.dmp,x2_exp.dmp,x3_exp.dmp log= x_exp.log compress=n rows=y constraints=y indexes=y grants=y direct=y consistent=y statistics=none buffer=150000000 filesize=1500M owner = xxx
11.4 Procedure to restore the Backup on the Different host on Different Directory Structure
The process to restore the backup on different host and on different directory structure Will be same as the restore that is explained in section 11.2 except for the following changes in the process Once the recovery of the database is over after the database is open with resetlogs Take a trace dump of the control file by using the command. SVRMGRL> Alter database backup controlfile to trace; Shutdown the Database Copy the trace file and modify the same ,change the name of the Server(Database) to the Change the path of the datafiles ,control files and the redo log files in the trace file ,this should point to the new target database and the respective correct file locations as per the new host Startup nomount; Run the trace file Rename the datafiles as per the new locations Alter database open resetlogs; After verifying the database notify the users that the refresh is done
11.5
Backup procedure for UK
The backup of the databases in the Lehman UK environment
Each of Databases the directory structure is as follows : /local/oracle//admin and under the admin directory all the other sub directories for that particular database exist, so the scripts directory under the admin directory contains all the scripts which are used for that particular database. The backup scripts used are dbbackup.ksh and kshtools, these two are the common scripts which is shared globally and used for backup of all the databases. The dbbackup.ksh script also generates a script called restore.ksh, this will be stored in the backup directory of that particular database and this is used for restoring the Database in case of problem. Apart from these scripts the script directory will have a script called dbbackup.env, this script is specific for that particular database and this contains all the configuration parameters /variables to backup to our requirements. the .env script contains the ORACLE_HOME,ORACLE_SID,ORACLE_LOGIN,path to backup directory,log directory ,export directory etc. This script also specifies the type of backup whether it is hot/cold ,rman backup,export backup , whether the schema's would require analyze after backup, pinning of packages. This script also specifys file for the Semaphore flag, that tells the other scripts that backup is in progress. KSHTOOLS is the script which has all the shell scripts /routines for the shell scripts which are used as part of the backup script. The Database backups are scheduled though Autosys tool as per the requirement of the user and the criticality/importance of the Database whether it is Production/Development
11.6 Autoload procedure for UK 11.6.1 AUTO LOAD FUNCTIONALITY DESCRIPTION
Autoload is a program used to automate the refresh of Oracle database schemas on Announcement Manager UAT systems. As it is currently used, the program is used to take an export dump from LONPAM01 and to refresh schemas on LONDAM01 and LONDAM02 using import, although in principle, the program could also be developed to refresh schemas between ANY two Oracle databases with a little further work. The auto load program directory structure is as follows (on host lonotgdevdbms1). Directory contents are described below the picture.
scripts: Following scripts are used by Autoload autoload.ksh : Script used to perform autoload autoload.env : Invoked by autoload.ksh to set parameters for autoload Routines : Contains some function routines used by autoload.ksh checkspace.sql : SQL*Plus script to check adequate space on tablespaces before autoload checkconnections.sql: SQL*Plus script to check for any session connected immediately before autoload getkills.sql : SQL*Plus script used to generate kills for sessions connected before autoload The scripts are attached below
The autoload.ksh is the main program which in turn calls the other scripts mentioned above. The autoload_IQAMSTAGE.env is the file which contains all the parameters required to run the auto load program. logs: This directory contains the log files generated from the autoload execution through autosys. templates: Contains template files for import, dropping and recreating users, used during autoload
load_area: Contains export file to be loaded and autoload run files. Contents are generated during autoload execution. Subsequent executions of autoload delete the files before recreating them.
11.6.2 AUTOLOAD PARAMETERS
All parameters for the autoload are sourced from the autoload.env file. This includes the source and target schema and databases for the autoloaded data. Usually, only the following parameters may need modification for an autoload into an Announcement Manager Database schema. LOAD_DATABASE - refers to ORACLE_SID of database being loaded LOAD_SCHEMA – Refers to schema being refreshed and repopulated using autoload LOAD_SCHEMA_PASSWORD – Usually set to the value of LOAD_SCHEMA, this can be altered afterwards to the desired value by the application team.
11.6.3 AUTOLOAD USAGE
Autoload can run in two modes: 1) Mode = checkonly. This mode performs a number of simple checks, and is usually run to maximise the chances of a subsequent full autoload succeeding as it generates warnings about possible failures during the full autoload. Checks that the export file exists Checks that adequate space is available in the load_area directory to copy export file to. Checks that adequate space exists in the tablespaces to load export file. On completion, the autoload will generate an email report to IQBox and londba with the results of the autoload.
Currently, autosys job DBA_ORACLE_AM_AUTOLOAD_CHECK is set to run this every Friday afternoon at 2pm, prior to the weekend‟s full autoload. 2) Mode = load. This mode performs all the checks performed in checkonly mode, but additionally it does the following. Generates autoload run files from templates Copies export file into load_area directory, and (optionally) performs a checksum to verify no corruption during file transfer Attempts to kill any sessions connected to the schema being refreshed using autoload Drops and recreates the schema to be refreshed Performs the import
Checks for any errors during import and generates an email report to IQBox and londba with the results of the autoload.
Currently, autosys job DBA_ORACLE_AM_AUTOLOAD is set to run the full autoload every Saturday morning at 6am. In order to run autoload sign onto host lonotgdevdbms1 as user oracle, make any modification necessary to the autoload.env file, and then force start the required autosys job (or get COMOPS to do it). If you run /local/oracle/stage/scripts/autoload.ksh On the command line, this will return the following usage instructions. Autoload usage error - please try again ======================================= Usage: autoload -m -c The -m flag is mandatory and is used to indicate the mode of operation The mode can be explicitly set to checkonly or load. checkonly mode ============== If set to checkonly, autoload will perform a series of checks and generate the results to standard output. The results indicate any potential problems with running autoload, such as a missing file Or inadequate space in the database or on the filesystem used to copy the export file to Load mode ========= If set to load, autoload will perform the checks and then if it finds results OK, will proceed to Load into the database The -c flag is optional and is used to switch on checksum mode (default is false) When supplied this will mean that the export dump file is checksummed with the source Prior to loading. If checksums do not match then file has been corrupted during the transfer from the source and autoload will abort
11.7
NET BACKUP EXCLUDED LIST
Net Backup
The location to find what is excluded from a host netbackup cat /usr/openv/netbackup/exclude_list
12
Standby Database
12.1 Procedure to Setup the Standby Database
The standby databases are set up as a backup for the primary database
CREATING A STANDBY DATABASE: =============================
1)
Ensure the Primary Database is in ARCHIVELOG Mode:
SQL> archive log list Database log mode Automatic archival Archive destination Oldest online log sequence Current log sequence SQL> alter database close; No Archive Mode Disabled x y
Database altered.
SQL> alter database archivelog;
Database altered.
SQL> shutdown immediate ORA-01109: database not open
Database dismounted. ORACLE instance shut down.
Modify the Primay database init.ora so that log_archive_start=true and specify a Archivelog Desintaion (log_archive_dest_1=), thenrestart the instance. Verify that database is in archive log mode and that automatic archiving is enabled.
SQL> archive log list Database log mode Automatic archival Archive destination Oldest online log sequence Next log sequence to archive Current log sequence Archive Mode Enabled x y y
2)
Create a full Backup of the Primary Database:
You can use an existing backup of the Primary database as long as you have the archive logs that have been generated since that backup. also take a You may
hot backup as long as you have all archive logs through the end of the backup of the last tablespace. To create a cold backup do the following:
SQL> SQL> select name from v$datafile;
NAME --------------------------------------------------------------------/export/home/oracle/temp/oracle/data/sys.dbf
SQL> SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit
Create a backup of all datafiles and online redo logs using an OS command or utility. A backup of the online redo logs is necessary to facilitate switchover.
Once complete startup the instance:
SQL> startup ORACLE instance started.
Database mounted. Database opened.
3)
Connect to your Primary Database and create the Stanby Controlfile:
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ;
4)
Copy Backup files to the Standby Host:
Copy the backup datafiles, standby controlfile, all available archived redo logs, and online redo logs from the primary site to the standby site. If you use FTP to copy the files, please mind to use BinaryMode (bin) for transfering the Files.
If the standby is on a separate site with the same directory structure as the primary database then you can use the same path names for the standby files as the primary files. In this way, you do not have to rename the primary datafiles in the standby control file. If the standby is on the same site as the primary
database, or the standby database is on a separate site with a different directory structure the you must rename the primary datafiles in the standby control file after copying them to the standby site. This can be done using the db_file_name_convert and log_file_name_convert INIT.ORAparameters or by manually using the ALTER DATABASE statements.
5)
Set the initialization parameters for the primary database:
Primary INIT.ORA:
log_archive_dest_1='LOCATION=' log_archive_dest_state_1=enable log_archive_format=%t_%s.dbf (MUST be the same as on Primiary !!) log_archive_start=true standby_archive_dest= If the directory structure is different you may also have to set on your Standby INIT.ORA:
control_files= background_dump_dest= user_dump_dest=
If you plan to run Primary and Standby database on the same Host, you must set the LOCK_NAME_SPACE-Parameter in the Standby-INIT.ORA to avoid Memoryconflicts in your System-Memory. You can set this Parameter to any up to 8 alphanumeric characters, mostly it is set to standby or the service/instance name. Please reference note 47340.1 for further information.
6)
Configure Networking Components:
Create a Net Service Name for the Standby database that the Primary Database is able to connect to the Standby Database. You may also configure a Listener on
the Standby Host.
Example TNSNAMES.ORA (on Primary Host):
Standby= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) ) (CONNECT_DATA = (SID = STANDBY) (SERVER = DEDICATED) ) )
Example LISTENER.ORA (on Standby Host):
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = STANDBY.world) (ORACLE_HOME = $ORACLE_HOME) (SID_NAME = Standby) ) )
7) WINDOWS NT/2000 only: Create a Windows Service for the new Standby Database
Before you startup the Standby instance, you have to create and start a Windows Service for this Standby Database. You can create this Service using the ORADIM-utility:
C:\>ORADIM -NEW -SID -INTPWD MAXUSERS n
Please see [NOTE:61621.1] for datails about ORADIM.
8)
Start the Standby Instance and mount the Standby Database:
sqlplus /nolog SQL*Plus: Release 8.1.7.0.0 - Production on Mi Okt 9 16:07:31 2002
(c) Copyright 2000 Oracle Corporation.
All rights reserved.
SQL> connect internal
Connected to an idle instance.
SQL> startup nomount pfile= ORACLE instance started.
SQL> alter database mount standby database;
Database altered.
SQL>
9)
Adjust Filenames if necessary:
SQL> alter database rename file 2 3 4 'old path/filename' to 'new path/filename';
10)
Start Listener:
Perform a LSNRCTL STOP and LSNRCTL START on Standby Site to make the Changes take effect.
11) Bounce Primary Database for Change of INIT.ORA-Parameters in 5) to take effect.
12) Verify that automatic archiving is occurring:
On the Primary database switch a log and verfy that it has been shipped properly using the v$archive_dest view.
SQL> alter system switch logfile;
System altered.
SQL> select status, error from v$archive_dest where dest_id=2;
STATUS
ERROR
--------- -----------------------------------------------------VALID
SQL>
13) Optionally place Standby Database in Managed Recovery:
SQL> recover managed standby database;
Your session will now just sit there. Logs will be applied as soon as they are transfered from the Primary Instance (see appropriate entries in the ALERT.LOG).
14) Stop the Managed Recovery:
Open a new Session and connect to the Standby Database:
SQL> recover managed standby database cancel;
Media Recovery canceled.
Optionally, you can now open the Database in READ-ONLY Mode:
SQL> alter database open read only;
Database altered.
12.2
Refreshing the Standby Servers
The steps for refreshing the Standby Servers can be found in the document attached below:
12.3
Procedure when a datafile is added to primary
To add a tablespace or datafile to the primary database and create the datafile in the standby database: 1.Create a tablespace on the primary database as usual. For example, to create new
datafile t_db2.f in tablespace tbs_2, issue: SQL> CREATE TABLESPACE tbs_2 DATAFILE 't_db2.f' SIZE 2M; 2.If the standby database is shut down, start the standby instance without mounting it. For example, enter: SQL> STARTUP NOMOUNT pfile=/private1/stby/initSTANDBY.ora
If the standby database is currently in managed recovery mode, skip to step 4. /* here you would skip to step 4 and not place the standby in managed recovery mode */ 3.Mount the standby database, then place it in managed recovery mode: SQL> ALTER DATABASE MOUNT STANDBY DATABASE; SQL> RECOVER MANAGED STANDBY DATABASE; 4.Switch redo logs on the primary database to initiate redo archival to the standby database: SQL> ALTER SYSTEM SWITCH LOGFILE;
If the recovery process on the standby database tries to apply the redo containing the CREATE TABLESPACE statement, it stops because the new datafile does not exist on the standby site. 5.Either wait for the standby database to cancel recovery because it cannot find the new datafile, or manually cancel managed recovery: SQL> RECOVER MANAGED STANDBY DATABASE CANCEL; Note that CREATE TABLESPACE redo adds the new filename to the standby control file. The following alert.log entry is generated: WARNING! Recovering datafile 2 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. Successfully added datafile 2 to media recovery Datafile #2: '/private1/stby/t_db2.f' 6.Create the datafile on the standby database. For example, issue: SQL> ALTER DATABASE CREATE DATAFILE '/private1/stby/t_db2.f'
AS '/private1/stby/t_db2.f'; 7.Place the standby database in managed recovery mode: SQL> RECOVER MANAGED STANDBY DATABASE; Continue normal processing on the primary database. The primary and standby databases are now synchronized.
12.4 Procedure to manually Synchronize the Standby Latency
Steps for Fixing the Standby latency problem 1 2 3 4 5 6 7 8 Open 3 Putty/exceed window. Window-1: Login on primary as oracle: Check the primary alert log and find out the latest log sequence number. Window-2:Login on standby server as oracle: Check the standby alert log and find out the log sequence number for standby instance is waiting. Window-2: cd /local/oracle/$ORACLE_SID/arch and find out the log missing from primary to secondary Window-2: check the disk space availability in the arch filesystem (df –k .) and determine how many missing log files can be transferred from primary to secondary. Window-3: Login on standby server as a oracle: cd /local/oracle/$ORACLE_SID/admin/bdump; tail –f alert_$ORACLE_SID.log Window-1: Transfer all missing log file using rcp src_file_name targethost:target_directory Window-2: make sure archive log is available in a sequence which are required for standby recovery and archive log files are not zipped otherwise unzip those archive log files by using gunzip. sqlplus /nolog connect / as sysdba alter database recover managed standby database cancel; recover standby database until cancel; Recover in AUTO mode During recovery keep checking the window-3 for errors. In case if any archive log file in a sequence having the problem then transffered those file again and restart the recover standby database until cancel; command on sqlplus prompt. Window-2: exit from sqlplus prompt and put the database in recovery mode. i. cd /home/dba/bin ii. ./SetRecoveryMode.ksh $ORACLE_SID & 10 Window-1: Switch the archive log file on primary and monitor the window-3 for progress of archive log on standby. i. sqlplus /nolog ii. connect / as sysdba iii. alter system switch logfile; If archive log has started moving from primary to standby and getting applied on standby automatic, it means both are in sync and
9
11
12.5
Refresh of Standby DB with Primary DB(for Syncing of Standby with Primary)
Syncing of Standby Database with Primary Database: The Synchronization of Standby with Primary is done along with the automated standby setup. The Standby is now in managed Standby Recovery mode. The following steps are involved which would be undertaken after the Groundwork for the setting up is done: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 rcp’ing the backup (inclusive of data dump, arch files and control files) to the Standby. Copying the control file (not Standby) to the respective destinations as mentioned in init file. On Standby , connect to svrmgrl ------> startup mount; exit; run the restore_.ksh script. (Once restore is done) On Standby, connect to svrmgrl ----> shutdown; exit; Copy the Standby Control file (backup copy of Standby) to appropriate locations as mentioned in the Standby init file. On Standby, connect to svrmgrl ----> startup nomount. On Standby , issue Alter Database Mount Standby Database; Recover Standby Database Until Cancel; (Archive logs brought from Primary are applied, exit the svrmgrl; On Primary, once all the archive logs are applied , exit the svrmgrl. cd /home/dba/bin/-----> run SetRecoverymode.ksh on Primary. Once the above shell script is run the database is set in ―Automated Standby mode‖. On primary, svrmgrl ----> Run the following SQL to verify Svrmgrl> select status, error from v$archive_dest where dest_id=2; The status should show as ―valid‖. Switch the log file in Primary and verify whether it has been applied in the Standby. Commented back cron job on Standby for Latency check and for Archclean.ksh.
12.6
PARTIONS ADDED ON NYFIDPROD6-M
Syncing STANDBY Database NJMERBAK
/dev/vx/rdsk/nyfidprod-datadg/NYMERPRD_efx_data_large_04 of Size 8193 MB /dev/vx/rdsk/nyfidprod-datadg/NYMERPRD_efx_indx_medium_05 of size 1155 MB ON NJORATTPRD01-M /dev/vx/rdsk/NJMERBAK_datadg/MER_efx_data_large_04 of Size 8193 MB /dev/vx/rdsk/NJMERBAK_datadg/MER_efx_indx_medium_05 of size 1155 MB ln -s /dev/vx/rdsk/nyfidprod-datadg/NYMERPRD_efx_data_large_04 efx_data_large_04.dbf ln -s /dev/vx/rdsk/NJMERBAK_datadg/MER_efx_indx_medium_05 efx_indx_medium_05.dbf
ON NYMERPRD Alter tablespace EFX_DATA_LARGE '/local/oracle/NYMERPRD/oradata1/efx_data_large_04.dbf' size 8192 m; add datafile
Alter tablespace EFX_INDEX_MEDIUM add '/local/oracle/NYMERPRD/oradata1/efx_indx_medium_05.dbf' size 1152 m; Alter system switch logfile; Alter system switch logfile;
datafile
Tail on Alert file of standby and see if the archive log application is erroring after the switch which confirms that the new datafile addition is recognized by the standby control control file. Now run the following commands. Alter database recover managed standby database cancel; Then add the datafiles as follows: alter database create datafile '/local/oracle/NYMERPRD/oradata1/efx_data_large_04.dbf' as '/local/oracle/NYMERPRD/oradata1/efx_data_large_04.dbf' size 8192 m;
12.7 12.7.1
1
Troubleshoot
Recreation of Standby Database from the RMAN backup
Start a SQL session on the standby database and issue the CANCEL statement on the standby database to halt its recovery process. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Shut down the standby database: SQL> SHUTDOWN IMMEDIATE; Start a SQL session on the primary database and create the control file for the standby database: SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stbycf.dbf'; Copy the standby control file and archived log files to the standby site and all RMAN backup files. Restore the backup using Restore.ksh ( Refer KAP doc for details). Startup nomount Connect to the standby database and mount (but do not open) the standby database: Restart the recovery process on the standby database using SetRecoveryMode.ksh script in /home/dba/bin directory.
2 3
4 5 6 7 8
9
To check whether standby dest is connected , run the foll at primary select status, error from v$archive_dest where dest_id=2;
13
Application Database
13.1 CAMEO
13.1.1 Application Description
Cameo is a FI application. It is a margin management system. This database was very old and there are many changes have been done in the scripts etc. Standards are not followed in this database. Most of the scripts are running using the Unix cron.
13.1.2 Environment Details
Database Environment: CAMEO Database is configured on 32 Bit Oracle 8.1.7.4.0 with 16K block size. 8 redo log files of with one member each of 400MB. Database buffers are configured for 1.2 GB at present and total SGA size is around 1.5GB. Total disk space is around 66GB. Total 1110 users are created in the database including database default schema. Cameo, cameorep and adpbridge are the main schema used. It gets the entity master and IDMS data from the Sybase database. Data gets replicated from DB2 also.
Data Flow Diagram (Draft copy)
NJFIRS11 Sybase DS - IDMS
NJFIRS9 Sybase DS - emaster
NJCAMEOPROD_RS NJCAMEOSY B1_DIRECT DB2
NJCAMEOSYBPROD Sybase DS CAMEO_DC
NJCAMEOSYBRI_DIRECT
Backup Server
IDMS emaster
cameorep ADPBRDGE CAMEO Oracle DB Primary Host: njcameoprod1
CAMEOPROD2 Oracle DB
Backup Host: njcameoprod2
Database Alias: cameohost
Application client: NT,XP,Win2K, Solaris
Description: Cameo data are replicated to cameo schema. More redo log switch is happening during backup time (17:00-18:00) and midnight (01:0002:00). Application on-call can be find out from group cameo_onduty or cameo_usa. In case of switching to backup database CAMEOPROD2, DBA needs to update QIP team to change the IP address of database alias ―cameohost‖ to njcameoprod2 host. After applying the
archive logs database need to be opened in RW mode. Since there is no failover for NJCAMEOPROD_RS, it needs to be rebuild on host njcameoprod2 and application user need to run their script to resync the replicated data. CAMEODEV is a development database. CAMEOQA is a staging database. DB2 replication puts data into the ADPBRIDGE schema.
13.1.3 Special DBA Tasks 13.1.3.1
1 2
Steps for the CAMEO Host Reboot
Wait for the page from SA for reboot. After getting page from SA, Ask Sybase DBA to shutdown following servers on njcameoprod1 in order listed below - NJCAMEOPROD_RS - NJCAMEOSYBPROD - NJCAMEOSYB1_DIRECT (Kill unix process) - CAMEO_DC (Kill Unix process)
3
Page user btell cameo,ADPBridge "FYI CAMEO oracle database server is going to be shutdown for SA planned … activity.. " $lsnrctl stop CAMEO Shutdown CAMEO SQL>SHUTDOWN IMMEDIATE; (Verify instance is down) Page SA that oracle database is down. btell njcameoprod1 "CAMEO database is down and ready for reboot.please page CAMEO once system is up." After getting page from SA, start the database and listener SQL> STARTUP lsnrctl start CAMEO Check the database connectivity from remote host. btell cameo,ADPBridge "FYI..CAMEO oracle database is up now" Ask Sybase DBA to start following servers on njcameoprod1 in order listed below - CAMEO_DC - NJCAMEOSYB1_DIRECT - NJCAMEOSYBPROD - NJCAMEOPROD_RS
4 1
2
3
4 5 6
7
Page DB2REP To start CAMEO replication.
13.1.3.2
CAMEO Replication Tricks
During 6PM to 9AM there are large activities in sybase idms env. So during this window you will see row diff and latency specially in DMS to CAMEO environment. As long as queues are moving and there are no threads down, it is OK. 1 2 Disable monitor on NJCAMEOPROD_RS fmsdisable -so NJCAMEOPROD_RS -ti 30 -note "DBA Bhavani" Login to Sybase replication server isql -Usa -SNJCAMEOPROD_RS -Pliberty83 ny2bo-xsrv1:/home/ghegde> isql -Usa -SNJCAMEOPROD_RS -Pliberty83 1> 3 Now suspend replication to CAMEO.IDMS schema and CAMEO.EMASTER 1> suspend connection to CAMEO_DC.emaster 2> go Connection to 'CAMEO_DC.emaster' is suspended. 1> suspend connection to CAMEO_DC.idms 2> go Connection to 'CAMEO_DC.idms' is suspended. 4 To check what has been suspended 1> admin who_is_down 2> go Spid Name State Info ---- ---------- ----------------------------------------------------------DSI EXEC Suspended DSI Suspended
5528(1) CAMEO_DC.emaster 5528 CAMEO_DC.emaster 5527(1) CAMEO_DC.idms 5527 CAMEO_DC.idms
DSI EXEC Suspended DSI Suspended
BTW - No data is lost, data is held in a store and forward file. 5 6 At this stage, IDMS and EMASTER replictaion parallel environment (not the real one used) is temporarily suspended. Now suspend replication to CAMEO.cameoprod schema.
This is production schema used by CAMEO. Note that IDMS and EMASTER schemas in CAMEO are not in use yet. 1> suspend connection to NJCAMEOSYBPROD.cameoprod 2> go Now, check what is suspended 1> admin who_is_down 2> go Spid Name State ---- ---------- ----------------------------------------------------------DSI EXEC Suspended DSI Suspended
Info
5528(1) CAMEO_DC.emaster 5528 CAMEO_DC.emaster 5527(1) CAMEO_DC.idms 5527 CAMEO_DC.idms 2325(1) NJCAMEOSYBPROD.cameoprod 2325 NJCAMEOSYBPROD.cameoprod
DSI EXEC Suspended DSI Suspended
DSI EXEC Suspended DSI 6 Suspended
At this point replication data flow from sybase to Oracle is stopped DB2 rep is still active. DO ALL THE ABOVE at 5:55 ===================================== WAIT until 18:20 Around 18:10 ... check the free space on replication Que 1> admin disk_space 2> go Partition Logical State Part.Id Total Segs Used Segs
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------------------------------------/dev/vx/rdsk/cameodg_data/sybstableq1 queue1 ON-LINE// 101 1000 7
PAY ATTENTION to "Total Segs" vs "Used Segs" Let us say, if the "Used Segs" exceeds 500, then resume suspended connections using commands below: ==== IF no exceptions as above - wait until 18:20 ======= Now, complete your time out tests ... If no time outs, we proved repserver transactions may be contributing to the problem. If time outs do occur ... u know what that means ... Now, resume connections -1> resume connection to NJCAMEOSYBPROD.cameoprod 2> go Connection to 'NJCAMEOSYBPROD.cameoprod' is resumed. 1> resume connection to CAMEO_DC.idms 2> go 1> resume connection to CAMEO_DC.emaster 2> go Connection to 'CAMEO_DC.emaster' is resumed. Now check if all connections have come up: 1> admin who_is_down 2> go Spid Name State ---- ---------- ----------------------------------------------------------1> Note .. blank line Now, enable monitor fmsdisable -en NJCAMEOPROD_RS NOTE: At time replication breaks and you may have to skip the few transactions. Inform the oracle dba and primary dba. We need to check for data inconsistency in the table with skipped transaction. Keep a eye on replication. If the replication breaks and we have tons of bad data sitting in the queue. Please get oracle dba and cameo user invloved at this point and keep everyone informed till the issue gets resolved.
Info
13.1.3.3
SOURCE DB:
Cameo Refresh
CAMEO
SOURCE SERVER: njcameoprod1 GTT@njoraprd1:/home/oracle> HostForServer CAMEO njcameoprod1 TARGET DB : CAMEOQA TARGET SERVER: nycameoqa GTT@njoraprd1:/home/oracle> HostForServer CAMEOQA nycameoqa Background and Important Notes: CAMEOQA has to be refreshed with data from Production over the Weekend having taken Friday's (April 18, 2003) backup. Production DB is CAMEO and SHOULD BE INTACT AT ANY COST. THIS IS IMPORTANT. Mandatory Points to note: The refresh job should be done over the weekend. The RMAN backup of the production DB is successful and complete on Friday night. This backup will be the SOURCE to refresh the CAMEOQA database. THE PRODUCTION BOX SHOULD BE INTACT AT ANY COST ALWAYS. Background: Daily DB RMAN backups are scheduled via Autosys and OraBackup.ksh is the script used for the same. Once the Production CAMEO DB RMAN backup is completed on the Unix Server njcameoprod1, the RMAN level 0 dump (backup), Archive logs and LogAfterDump files should be shipped (via rcp) to the TARGET box, where CAMEOQA is residing. Procedure: On TARGET Server---nycameoqa 1 2 3 Check to see if any cronjob is running to cleanup archive logs for the DB. Get the password for system for CAMEOQA by executing getsap. Disable Monitoring by issung: g2disable -so CAMEOQA -ti 1200 Check to see if the link CAMEO exists and should be pointing to CAMEOQA. CAMEO -> /local/oracle/CAMEOQA in /local/oracle---------------->(EXISTING) On SOURCE MACHINE---- njcameoprod1 Copy the following files to the target SOURCE LOCATION TARGET LOCATION
4
/local/oracle/CAMEO/backup-------------------->/local/oracle/CAMEOQA/backup /local/oracle/CAMEO/arch----------------------->/local/oracle/CAMEOQA/arch /local/oracle/CAMEO/arch/LogAfterDump----->/local/oracle/CAMEOQA/arch/LogAfterDump On TARGET Server--------nycameoqa.
5
cd /local/oracle/CAMEOQA/arch rename all copied archive logs ... for eg: CAEMO.log.0001.0000000174.LogAfterDump to CAEMO.log.0001.0000000174.
6 7
Shutdown CAMEOQA Database. Copy the following controlfiles from source to target. Source Location CAMEO@njcameoprod1:/local/oracle/CAMEO/backup> ls *.ctl CAMEO.133782.ctl CAMEO.133782.stdby.ctl Find out where the controlfiles are residing in the target DB CAMEOQA. /local/oracle/CAMEOQA/oradata1/CAMEOQA_1.ctl /local/oracle/CAMEOQA/oradata2/CAMEOQA_2.ctl Copy the Controlfile to the above two locations on the target server.
8
Modify the following parameters as mentioned in initCAMEOQA.ora file on the target server. Take a copy of the init file cp initCAMEOQA.init initCAMEOQA_041803.ora Parameters to be modified are: db_name=CAMEOQA-------------> CAMEO instance_name=CAMEOQA----->CAMEO service_names=CAMEOQA ----->CAMEO control_files = (/local/oracle/CAMEOQA/oradata1/CAMEOQA_1.ctl,/local/oracle/CAM EOQA/oradata2/CAMEOQA_2.ctl)---------->NO CHANGES user_dump_dest = /local/oracle/CAMEOQA/admin/udump-------->NO CHANGES background_dump_dest = /local/oracle/CAMEOQA/admin/bdump-->NO CHANGES core_dump_dest = /local/oracle/CAMEOQA/admin/cdump------------->NO CHANGES audit_file_dest = /local/oracle/CAMEOQA/admin/adump -------------> NO CHANGES log_archive_format ="CAMEO.log.%T.%S"-------------------------------------> NO CHANGES
9 10
Mount the CAMEOQA DB. Run the restore script, which does restore from RMAN. Run the script restore_CAMEOQA.ksh which in turn calls restore_CAMEOQA.rman. nycameoqa% cd /home/dba/scripts nycameoqa% ls -ltr restore* -rwxr-xr-x 1 oracle 429 Apr 17 14:19 restore_CAMEOQA.rman -rwxr-xr-x 1 oracle 299 Apr 17 14:30 restore_CAMEOQA.ksh The restore_CAMEOQA.ksh scripts calls the rman script, which does the restore job.
11
Once restore is successful, run the following from svrmgrl and apply all the archive logs.
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL. 12 13 14 Issue: ALTER DATABASE OPEN RESETLOGS; Checks: Check to see if all tablespaces are online and DB is in read/write mode. Issue following from svrmgrl: ALTER DATABASE BACKUP CONTROLFILE TO TRACE; Make a note of the trace file generated in the udump directory SHUTDOWN IMMEDIATE; Modify initCAMEOQA.ora file on the target side as follows: db_name=CAMEO-------------> CAMEOQA instance_name=CAMEO----->CAMEOQA service_names=CAMEO ----->CAMEOQA log_archive_format = "CAMEO.log.%T.%S" 17 Get the trace file name from step 14. Modify the trace file name under the directory /local/oracle/CAMEOQA/admin/bdump to create a new controlfile from the existing one. Replace CAMEO with CAMEOQA in the Controlfile script. CREATE CONTROLFILE REUSE SET DATABASE "CAMEOQA" RESETLOGS ARCHIVELOG...... COMMENT OUT RECOVER DATABASE -------Command. Shutdown and Startup the DB gracefully. Make Sure TEMP Tablespace is NOT Empty. Change password for SYS and System that matches with getsap. Password for Sys and System are same. Check the following at the target DB. Check for the invalid procedures and compile the same. Check to see if all tablespaces are online. Check to see if DB is in Read/Write Mode.
15 16
18 19 20 21
13.1.3.4
Procedure to Sync Cameo Data
Note -Ideal time to sync the database is after 4PM EST when there are less activities in idms environment. But at times we may have to do it as there is lots of bad data lying in the queue and the latency keeps building. 1 2 3 4 Suspend the routes from NJFIRS1, NJFIRS9 to NJCAMEOPROD_RS. In NJCAMEOPROD_RS purge the queue. In NJCAMEOPROD_RS suspend the connections. User will to the manually sync the data.
5 6 7 8 9
After the user is done with the sync. In NJCAMEOPROD_RS resume the connections. Resume the routes from NJFIRS1, NJFIRS9 to NJCAMEOPROD_RS. There may be few duplicates coming in and you will have to skip them. Keep an eye on replication for a while.
13.1.3.5
Commands
njbo-xsrv4:/home/ghegde> isql -Usa -SNJCAMEOPROD_RS -Pliberty83 1> admin who_is_down 2> go Spid Name State Info ---- ---------- ----------------------------------------------------------1> If you see something like 1> admin who_is_down 2> go Spid Name State Info ---- ---------- ----------------------------------------------------------DSI EXEC Suspended 2325(1) NJCAMEOSYBPROD.cameoprod DSI Suspended 2325 NJCAMEOSYBPROD.cameoprod A. B. It is a problem, contact sybase Check database server
njbo-xsrv4:/home/ghegde> isql -Usa -SNJCAMEOSYBPROD -Pliberty83 1> 2> 3>quit
13.1.4 Troubleshooting
a) If CAMEO_DC.idms is down and giving constraint violation error than transaction can be skipped and restarted as mentioned below. 1 Connect to Replication server and start the transaction with skip option. CAMEO@njcameoprod1:/home/oracle> isql -S NJCAMEOPROD_RS -P -U sa 1> who is down 2> reset 1> admin who_is_down 2> go
Spid Name State Info ---- ---------- -------------------- ---------------------------------------DSI EXEC Suspended 5527(1) CAMEO_DC.idms DSI Suspended 5527 CAMEO_DC.idms 1> resume connection to CAMEO_DC.idms skip tran 2> go Connection to 'CAMEO_DC.idms' is resumed. 1> admin who_is_down 2> go Spid Name State Info ---- ---------- -------------------- ---------------------------------------DSI EXEC Suspended 5527(1) CAMEO_DC.idms DSI Suspended 5527 CAMEO_DC.idms 1> resume connection to CAMEO_DC.idms skip tran 2> go Connection to 'CAMEO_DC.idms' is resumed. 1> admin who_is_down 2> go Spid Name State Info ---- ---------- -------------------- ---------------------------------------1>
2
Monitor log file of NJCAMEOPROD_RS and make sure that resume connection is not giving any error message. Login as a Sybase. njcameoprod1:/usr/local/sybase/install> tail -f errorlog_NJCAMEOPROD_RS I. 2003/12/27 00:49:45. A grouped transaction of 20 individual transactions has failed in database 'CAMEO_DC.idms'. Each transaction in the group will be executed individually. E. 2003/12/27 00:49:45. ERROR #1028 DSI EXEC(5527(1) CAMEO_DC.idms) dsiqmint.c(2892) Message from server: Message: 1, State 0, Severity 16 -- 'ORA-00001: unique constraint (IDMS.PNLCURRENT_IDX1) violated '. H. 2003/12/27 00:49:45. THREAD FATAL ERROR #5049 DSI EXEC(5527(1) CAMEO_DC.idms) - dsiqmint.c(2899) The DSI thread for database 'CAMEO_DC.idms' is being shutdown. DSI received data server error #1 which is mapped to STOP_REPLICATION. See logged data server errors for more information. The data server error was caused by output command #1 mapped from input command #1 of the failed transaction. I. 2003/12/27 00:49:45. The DSI thread for database 'CAMEO_DC.idms' is shutdown. I. 2003/12/27 00:50:06. The DSI thread for database 'CAMEO_DC.idms' is started. I. 2003/12/27 00:50:10. The first transaction for database 'CAMEO_DC.idms' has been logged into the exceptions log and skipped. I. 2003/12/27 00:51:26. ...... connected to server 'NJCAMEOSYBPROD' as user 'NJCAMEOPROD_RS_RSSD_maint'.
b) CAMEO.LegHeader table has less rows than Sybase database DMS_PROD.idms from where the data is replicated via sybase replication server.
Description: Many tables are replicated from Sybase Database DMS_PROD.idms to Oracle server CAMEO via NJFIRS1. Troubleshooting steps: 1 Log into DMS_PROD.idms (Sybase), check row count for LegHeader 1> select count(*) from LegHeader 2> go ----------466740 2 Login to CAMEO SQL> select count(*) from CAMEOREP.LegHeader; COUNT(*) ---------466739 3 The above counts should be same. In addition, the content of the rows should be same .. That is next headache !! In example above, one row is missing in Oracle, we need to find and fix it .. 4 Now narrow down the range (If you can) On DMS_PROD.idms select count(*) from LegHeader LH, LegDetail LD where LH.id = LD.id and LD.busDate > '2/10/2004' ----------58421 (1 row affected) On CAMEO, select count(*) from CAMEOREP.LEGHEADER LH, CAMEOREP.LEGDETAIL LD where LH.id = LD.id and LD.BUSDATE > '10-FEB-2004' ; COUNT(*) ---------58421 This means that there was no problem from '10-FEB-2004'. Continue the search.
Sybase select max(id) from LegHeader Oracle Sybase 1> select count(*) from LegHeader where id > 1000000 and id go ----------43893 (1 row affected) Oracle SQL> select count(*) from CAMEOREP.LegHeader where id > 1000000 and id 2000001; COUNT(*) ---------43892 We got the missing row in this RANGE .... Lucky you !! 1> select count(*) from LegHeader where id > 1483000 and id go ----------122 (1 row affected) SQL> select count(*) from CAMEOREP.LegHeader where id > 1483000 and id SQL> select count(*) from LegHeader where id > 1483300 and id go ----------20 (1 row affected) SQL> select count(*) from CAMEOREP.LegHeader where id > 1483300 and id 1483400 select max(id) from CAMEOREP.LegHeader;
2 ; COUNT(*) ---------19 SOLUTION : DO NOT TRY THIS WITHOUT 1. USER APPROVAL. 2. HAVE A SENIOR SYBASE DBA help in the process. 3. Let sybase DBA create a confif file and run rs_subcmp on njcameoprod1. njcameoprod1:/opt/sybase/REP-12_1/REP-12_1/bin> cat LegHeader_20040224.cfg # Reconcile RDS.RDB..RTABLE with PDS.PDB.PTABLE data PDS = NJFISYB1 PDB = idms PTABLE = LegHeader PSELECT = select * from LegHeader where id > 1483300 and id 1483300 and id rs_subcmp LegHeader_20040224.log & The log files will have differences. Disclaimer Technique above is applicable to above problems only. And, it differes from case to case. And, be careful when running dynamic queries on servers (you may cause table scans that consume a lot of resources !!) MOST IMPORTANT CAMEO development team has script to refresh all replicated tables. At times, it is easy to refesh all tables at a quite point rathar than going through debug sessions above. -f LegHeader_20040224.cfg >
Implementation a. Now discuss with users. b. Let sybase DBA modify the script for FIX and run it, OR - Sybase DBA will change LegHeader_20040224.cfg RECONCILE = Y - Run rs_subcmp to fix it njcameoprod1:/opt/sybase/REP-12_1/REP-12_1/bin> rs_subcmp -f LegHeader_20040224.cfg > LegHeader_20040224_fix.log & c. Let CAMEO team refresh all tables from DMS_PROD.idms and ENTIRYPROD_A.emaster
Any question's call Gajanan TEST AFTER FIX In the above case: In Sybase 1> select count(*) from LegHeader where id > 1483300 and id go ----------20 In Oracle SQL> select count(*) from CAMEOREP.LegHeader where id > 1483300 and id 1483400 2 ; COUNT(*) ---------20 It is fixed. But, do not get too excited, the row count match is just a tip of the iceberg.. Row count could be same, but row content could be baa....d. It is the next story ....
13.2 13.2.1 Application Description
RAPTOR
The RAPTOR application consists of the following databases NYRAPPRD , NJRAPPRD Production Databases and the staging databases NJRAPHIS and NYRAPHIS NYRAPPRD and NJRAPPRD are the most critical databases for Lehman Brothers
13.2.2
Environment Details
NYRAPPRD SunOS release 5.8 Generic_117000-05 Oracle Binaries are: 64-bit oracle/9.2.0 50 users SGA 1.5 GB Total Disk space 100 GB 5 groups with one member each of 300 MB NYRAPHIS SunOS release 5.8 Generic_117000-05 oracle/9.2.0 Oracle Binaries are: 64-bit 47 uses SGA 1.8 GB Total disk space 20 GB 6 groups with 1 log member each of 300 MB NJRAPPRD SunOS release 5.8 Generic_117000-05 oracle/9.2.0 Oracle Binaries are: 64-bit SGA 1.91 GB 46 users 5 groups of redologs with 1 member each of 300 MB Total Disk space is 35 MB
NJRAPHIS SunOS release 5.8 Generic_117000-05 oracle/9.2.0 Oracle Binaries are: 64-bit SGA 1.89 GB 5 groups of redologs with 1 member each of 300 MB 47 users total disk space is 56 GB
13.2.3
Special DBA Tasks
The Databases NYRAPPRD and NJRAPPRD are supporting the same application RAPTOR The RAPTOR application writes to both the databases simultaneously and at the application level the database NJRAPPRD is backup of the database NYRAPPRD
If problem occurs at the application level and if the application has to failover from NYRAPPRD to NJRAPPRD then the DBA has to co-ordinate with the SA to get the QIP of raptorhost changed to the new host to which the application has failed over.
13.2.4 Troubleshooting
Any problems in the RAPTOR database , DBA has to immediately inform the RAPTOR group . Needs to keep an eye on the Performance of the RAPTOR database and also to verify the database space reports regularly to ensure that the database does not fail due to space issues and also does not degrade due to performance issue‟s. Also DBA needs to watch the archive log destination growth and the space on the file system & see to it that it does not get filled up quickly to avoid database hanging problem./Time out problem
13.3 13.3.1 Application Description Lehman Live
This is the official Website of the Lehman Brothers and the databases involved is LLPROD and NYLLPROD LLPROD and NYLLPROD are both primary databases NYLLPROD acts as a DR for the primary database LLPROD
Environment Details
The environment/configuration details of the Production Databases LLPROD and NYLLPROD are as follows NYLLPROD SunOS 5.8 Release SGA – 4.18 GB Oracle 8i Enterprise Edition Release 8.1.7.4.0 64 Bit Production There are 5 groups of redo logs with one member each and of size 100 mB The Database is under Veritas Cluster System (VCS) LLPROD Sun OS 5.8 Release Oracle 8i Enterprise Edition Release 8.1.7.4.0 64 Bit Production The database has 5 groups of redologs consisting of 1 member each and of 100 MB size SGA – 3.4 GB The database is under VCS and the precautions that needs to be taken are mentioned in the next section
13.3.2
Special DBA Tasks
In case there is a problem on LLPROD the applications have to be failed over to NYLLPROD. Here the there is automatic failover of applications from LLPROD to NYLLPROD Here the Data is replicated from LLPROD to NYLLPROD through Shareplex replication tool and the dba needs to make sure that the data/shareplex is in Sync on NYLLPROD and LLPROD If a situation occurs where Application needs to be pointed to NYLLPROD from LLPROD then the DBA has to inform the ecommerce team , so that they can repoint the Application to NYLLPROD instead of LLPROD If in case LLPROD database has to be brought down Then the DBA has to Coordinate with the SA as the Database is under Veritas Cluster System (VCS) and also the shareplex has to be brought through VCS The database should be shutdown/started using the /home/dba/bin/oractl utility
13.3.3 Troubleshooting
The Data is being written to NYLLPROD database simultaneously As the data is being replicated using Shareplex replication tool from the Primary Production database LLPROD DBA has to see that Database NYLLPROD is always in sync with LLPROD Also the DBA should monitor the Space details of the tablespaces on the database to avoid any breakdowns due to space issues, as the database is very critical and keeps the official website of Lehman Brothers up and running
13.4 13.4.1 Application Description GTT
GTT is the application which deals with government trades etc , here the Volume of transactions that are performed are very high and the number of users will be comparatively low The application is the most critical applications in Lehman and is the most important /critical databases of lehman brothers, this is the application where Lehman gets most of its revenue‟s
13.4.2 Environment Details
SGA – 1.12 GB Oracle 8i Enterprise Edition Release 8.1.7.4.0 64 Bit Production The database consists of 4 groups of Redologs with one member each of 50 MB size The Database is under VCS , Startup/shutdown has to done with coordination of the SA the disk size is 34 GB
13.4.3 Special DBA Tasks
If in case GTT database has to be brought down Then the DBA has to Coordinate with the SA as the Database is under Veritas Cluster System (VCS)
Also the DBA has to inform the users about the shutdown and also the duration for which the database will be down and also the reason for which the database will be shutdown If the database goes down due to host problem or some other database issues , then the DBA has to raise an LL incident report and update the same with all the details DBA needs to monitor the space report of the database on a daily basis and see that there is no problems due to the space issue on the database
13.4.4 Troubleshooting
GTTPRD is the most critical database used for Trading services in Lehman Environment. DBA should monitor the space on all the tablespace, also on all the file systems , and make sure that the service /database does not go down due to space issuses on the host Also DBA has to keep watch of the archive log destination file system , so that it does not get filled up, which in turn causes time out problems for the database .
13.5 13.5.1 Application Description
NYESERV
NYESERV database is the database used for Ecommerce Application at Lehman Brothers.
13.5.2 Environment Details
Sun OS Release 5.8 Oracle 9i Enterprise Edition Release 64Bit Production SGA 3.05 GB The Database has 4 groups of Redo logs with one member each and of size 200 MB The Database is under Veritas Cluster System (VCS)
13.5.3 Special DBA Tasks
As the Database NYESERV is under VCS , startup and Shutdown of the Database should be done with the Co-ordination of the Unix SA
13.5.3.1 NYESERV Support process
A special monitor has been enabled on NYESERV. The message is as follows : A02578283 06/30/2004 15:56 root ALL BTELL [NYESERV]: nyecomdb3-m is overloaded(loadindex= 21.89), pls check what is going on. loadindex= 21.89 This page will come out whenever there is a potential issue on NYESERV. If that happens, take following steps/actions: 1 2 Identify ADB_POOL_USER active sessions that are consuming lot of CPU. Get queries of all these sessions, if not possible some sessions.
3 4
Kill the sessions in step 0. btell hmiranda,lgupta NYESERV ADB_POOL_USER queries causing load killed.
You may use DB Artisan, OEM and following technique outlined by Bhavani: 1 Login as oracle on nyecomdb3-m 2 Issue top. 3 Get the process id number at unix level that consumes most CPU. 4 Login as system@NYESERV. 5 @/home/dba/sql/current_user_activity_in_db.sql. 6 View /home/dba/log/SID_current_user_activity.log. 7 View /home/dba/log/SID_current_user_activity.log and search for the OS process number from step 2.
13.5.4 Troubleshooting
Quick Troubleshooting procedure A Quick process to bring Host load nyecomdb3-m under control: 1 2 Open DBArtisan and connect to NYESERV . Click on Instance/Process tab and keep it always open. Have following telnet/Xceed session open always: A. Nyecomdb3-m:oracle --> /home/dba/sql B. nyecomdb3-m:oracle---->home/dba/log C. nyecomdb3-m:oracle---->sqlplus /nolog D. nyecomdb3-m: oracle--> Issue top When DBA receives alerts from isgweb@lehman.com. What to do? Issue Top If load average is 10%-- 15%, then monitor the SYSTEM Run Nyecomdb3-m: oracle: /home/dba/sql/GET_NYESERV_REPORT.ksh Issue sqlplus from remote host-->if this process timeouts, then it's time to take action. If you see the load increasing, Btell hmiranda,lgupta,yosuke "Can active sessions be killed on ADB_POOL_USER-dba- 1 800 666 2388 xtn " On confirmation from user start Killing ACTIVE ADB_POOL_USER Connections ONLY. DO NOT DISTURB ESERVICES Connections. If load average is >-40% Run Nyecomdb3-m: oracle: /home/dba/sql/GET_NYESERV_REPORT.ksh Then Kill all active ADB_POOL_USERS from (Dbartisan). Straight away even before any INSTRUCTION CAN Come from USER or Escalated contacts. Sort by Status and Kill ACTIVE ADB_POOL_USER connections Page ghegde:ora_dba;hmiranda,lgupta,yosuke "NYESERV Host load back to normal XX% -DBA -contact n"
3
13.6 13.6.1 Application Description 13.6.2 Environment Details 13.6.3 Special DBA Tasks 13.6.4 Troubleshooting
ADB
13.7 13.7.1 Application Description 13.7.2 Environment Details 13.7.3 Special DBA Tasks 13.7.4 Troubleshooting
R2R
14
Veritas Cluster SetUp
14.1 DBA part in cluster configuration.
DBA plays an important role in the Cluster Configuration of any database /host .Oracle Dba needs to co-ordinate with the Unix SA to set the parameters which are connected to the database like the listener parameter etc Dba needs to know the functionalities and also the impact when the Database is included under the VCS coverage When some scheduled work has to be done on the database, Dba has to co-ordinate with the SA to avoid the VCS errors from occurring
14.2 Cautions to be taken in handling Databases & Resources when it comes to VCS
Under VCS Dba should be very cautious while handling the databases . When ever the Databases under VCS have to be started /shutdown, he has to inform the SA about this , so that the SA can bring down the Database through VCS . Suppose the Database goes down accidentally ,then the DBA has to inform thr SA to clear the VCS fault. DBA has to check whether the Database has failed over to the other node ,if so he needs to inform the user‟s that the database has failed over and to point the application to the new host and also dba has to put host name in the Global tnsnames ora file and push the same so that it comes to effect.
15
Shareplex
15.1 Shareplex Learning
Shareplex Documentation - Applicable to Lehman Ecommerce environments,where replication is set up via Shareplex (Quest).
15.1.1 Documentation Available At
All ecommerce hosts like njecomdb11-m, njecomdb1/2-m. njecomdb10-m:/home/dba/files/SHAREPLEX#ls -ltr total 14 -rw-r--r-- 1 oracle dba 667 Dec 11 2002 addtables -rw-r--r-- 1 oracle dba 1573 Dec 11 2002 addcolumn -rw-r--r-- 1 oracle dba 1076 Jan 9 15:19 apply_patch drwxr-xr-x 2 oracle dba 96 Jan 24 15:34 test_script -rw-r--r-- 1 oracle dba 861 Mar 28 14:15 commands -rw-r--r-- 1 oracle dba 780 Apr 8 14:38 skip_tran + dbaweb – Where ?
15.1.2 Important Base file with Basic Commands
more commands njecomdb10-m:/home/dba/files/SHAREPLEX#more commands For QA ETGQA_S and ETGQA_T, Unix login access splexadm/splexadm_pass For Stage ETGSTG_S and ETGSTG_T , Unix login access splexstg/splexstg_pass Check env for logs, scripts, bin, etc. Logs 1) 2) error_log (event_log) is under $SP_SYS_VARDIR/log logs from compare process *declt* client process (on replicate side) *desvr* server process (on primary side) (usually runs from 2:00 a.m. - 5:00 a.m.) are under $SP_SYS_VARDIR/log/cmpconfig.$date. Startup/Shutdown /local/0/opt/splex/scripts/$ORACLE_SID/splex_start.sh /local/0/opt/splex/scripts/$ORACLE_SID/splex_shut.sh
Interactive command line sp_ctrl Check Queue and status status qstatus show post detail show capture detail Check event_log show log Start/Stop queue Log on to interactive command line sp_ctrl: start/stop $QUEUE_NAME For example: start post stop capture njecomdb10-m:/home/dba/files/SHAREPLEX#
15.1.3 Table – depicting replication flow details for PROD, STAGE and QA Ecommerce environments
Category Source Primary Host Name/ DBName Njecomllprod / LLPROD Njecomdb10m/ ETGSTG_S Njecomdb10m/ ETGQA_S Source Host Alias Name Njecomdb1-m Target Replicate Sitehost name/DBName Nyecomllprod/ NYLLPROD Njecomdb11-m/ ETGSTG_T Njecomdb11-m/ ETGQA_T Target Alias name Nyecomdb1-m User ids Shareplex splexprod splexstg splexadm
Production Staging QA
15.1.3.1
Points to remember
Only Prod ecommerce environments are configured to operate on VCS clusters unlike QA and STG. This means that Shareplex will failover in case of outages on the primary only in Production environments. This calls for the following attention from DBAs: Ecommerce environments especially production, no downtime can be tolerated during the business hours. For DB Startup/Shutdown, Users should be informed first and SA should be kept in loop (in case of Prod- for VCS cluster management at OS level).
In case of emergency Shareplex/replication operations on Production, only the replication/Shareplex operations, if need be could be restarted during OFF hours, even if SA’s are not informed. However ISG team should always be kept in loop. This means ideally, ISG group should be informed first and the user community in case of any scheduled activities. ISG group will pitch in to extend OS level VCS support in clearing the fault if any and to bring the SG to the normal mode. To conclude DB level startup/shutdown in production should be done via VCS, that’s via SA’s and NOT MANUALLY. However Shareplex/replication oriented processes can be restarted manually, if need be during OFF hours and ISG should be kept in loop always. Users also need to be informed in case of any replication oriented activities on the DBs, since it would affect the replicated data at the target end. QA and STG ecommerce environments are operating on local disks at the OS level. This means there is no VCS cluster management available with STG and QA ecommerce environments.
15.1.4 Prime End User Contacts
Blair,Deron@lehman.com Suzuki,Yosuke@lehman.com
15.1.5 Checklist to verify the right ecommerce environment details with reference to Shareplex.
First of all log into the right Unix host as splexadm for Shareplex administrative/maintenance related activities. One can find out , if he/she has logged into the right env or not by doing the following: Login as splexadm and issue env. More details on specific findings-See below: Significant Point to note: Permanent/Hard host name is used in case of STG and QA ecommerce environments unlike PROD. For eg: njecomdb10-m:/home/splexadm#id uid=30682(splexadm) gid=36001(spadmin) njecomdb10-m:/home/splexadm#env _=/bin/env MANPATH=/opt/gnu/man:/export/home/man:/usr/share/man:/usr/local/man HZ=100 PATH=/opt/oracle/8.1.7:/opt/oracle/8.1.7/bin:/home/dba/bin:/bin:/opt/bin:/usr/bi n:/usr/sbin:/usr/ccs/bin:/usr/ucb:/usr/lib/X11:/opt/gnu/bin:/usr/openwin:/local/ 0/opt/splex/bin:/local/0/opt/splex/ETGQA_S/splex_data SP_SYS_VARDIR=/local/0/opt/splex/ETGQA_S/splex_data EDITOR=vi LOGNAME=splexadm
SP_COP_UPORT=2100 SP_COP_TPORT=2100 TARGET_HOST_NAME=njecomdb11-m ORACLE_SID=ETGQA_S PS1=njecomdb10-m:${PWD}# SHELL=/bin/ksh HOME=/home/splexadm SP_SYS_BINDIR=/local/0/opt/splex/bin TERM=ansi ORACLE_HOME=/opt/oracle/8.1.7 PWD=/home/splexadm SP_SYS_DIR=/local/0/opt/splex SP_SYS_HOST_NAME=njecomdb10-m TZ=US/Eastern
15.1.5.1
Inferences
ORACLE_SID should give you the Oracle Instance Name. TARGET_HOST_NAME gives the name of the replicate or the target site or Unix host name. SP_SYS_HOST_NAME gives the name of the source site or the Unix host name which is acting as the source for Shareplex replication. SP_COP_UPORT and SP_COP_TPORT, indicates the unique ports for identified Shareplex environments. FYI—All Shareplex processes and DIR mostly start with SP% FYI---SP_COP is the parent Shareplex process. In Lehman ecommerce world, current standards are as follows: PORT 2100 for Ecommerce QA environment. PORT 2200 for Ecommerce STG environment. PORT 2300 for Ecommerce PROD environment. Shareplex Unix level User id is splexadm. Shareplex Oracle DB Level User is splex. SP_SYS_VARDIR gives the location of the actual Shareplex oriented Directories starting from config, log, dumps etc..
15.1.6 A glimpse at the Shareplex directory Structures
njecomdb11-m:/local/0/opt/splex/ETGQA_T/splex_data#env | grep VARDIR SP_SYS_VARDIR=/local/0/opt/splex/ETGQA_T/splex_data njecomdb11-m:/local/0/opt/splex/ETGQA_T/splex_data#cd $SP_SYS_VARDIR njecomdb11-m:/local/0/opt/splex/ETGQA_T/splex_data#pwd /local/0/opt/splex/ETGQA_T/splex_data njecomdb11-m:/local/0/opt/splex/ETGQA_T/splex_data#ls -ltr total 130252 drwxrwxr-x 2 splexadm spadmin 512 Jul 10 2002 idx -rw-r--r-- 1 root other 66449408 Jul 14 2002 qasplex_data.tar drwxrwxr-x 2 splexadm spadmin 111104 May 2 05:46 dump
drwxrwxr-x drwxrwxr-x drwxrwxr-x drwxrwxr-x drwxrwxr-x drwxrwxr-x drwxrwxr-x
38 splexadm spadmin 2 splexadm spadmin 2 splexadm spadmin 2 splexadm spadmin 2 splexadm spadmin 2 splexadm spadmin 2 splexadm spadmin
60416 Jun 4 13:41 log 512 Jun 4 16:40 config 512 Jun 4 16:43 db 512 Jun 4 16:43 save 512 Jul 1 05:44 data 3072 Jul 3 16:33 rim 3072 Jul 3 17:55 state
15.1.7 Sample contents of DIRs under $ SP_SYS_VARDIR 15.1.7.1 Sample dump contents
lnjecomdb11-m:/local/0/opt/splex/ETGQA_T/splex_data/dump#ls -ltr total 499744 -rw------- 1 root spadmin 6028 Apr 20 13:21 core.23985 -rw------- 1 root spadmin 14196264 Apr 20 13:22 core.24172 -rw------- 1 root spadmin 14210124 Apr 20 13:22 core.24157 -rw------- 1 root spadmin 14196264 Apr 20 13:23 core.24418 Note: Dump DIR contains the core dumps generated due to errors, which are owned by root.
15.1.7.2
Sample log contents
drwxr-xr-x 2 splexadm spadmin 112128 Apr 21 05:02 cmpconfig.04212003 drwxr-xr-x 2 splexadm spadmin 51712 Apr 22 05:05 cmpconfig.04222003 -rw-r--r-- 1 splexadm spadmin 198100 Mar 19 14:29 event_log_primary -rw-rw-r-- 1 root spadmin 1621 Mar 19 15:53 ETGQA_T_desvr-15462-01.log -rw-rw-r-- 1 root spadmin 1627 Mar 19 16:21 ETGQA_T_desvr-20042-01.log -rw-rw-r-- 1 root spadmin 5090 Apr 14 18:32 ETGQA_T_fb_9242_5.log -rw-rw-r-- 1 root spadmin 5090 Apr 14 18:57 ETGQA_T_fb_5246_5.log -rw-r--r-- 1 splexadm spadmin 0 Apr 20 05:20 report.log -rw-r--r-- 1 splexadm spadmin 141565 Apr 20 11:33 ETGQA_T_eventlog.txt -rw-rw-r-- 1 root spadmin 2670 Apr 20 15:03 ETGQA_T_recover_7252.log -rw-rw-r-- 1 root spadmin 30504 Jun 4 13:41 ETGQA_T_errlog.sql -rw-rw-r-- 1 root spadmin 30183116 Jun 4 16:36 ETGQA_T_ovr01.log -rw-rw-r-- 1 root spadmin 2163 Jun 4 16:43 ETGQA_T_log.rmp -rw-rw-r-- 1 root spadmin 4558 Jul 1 05:41 ETGQA_T_ord01.log -rw-rw-r-- 1 root spadmin 10835 Jul 1 05:43 commands_log -rw-rw-r-- 1 root spadmin 2693369 Jul 3 04:10 ETGQA_T_ocap01.log -rw-rw-r-- 1 root spadmin 2878454 Jul 3 12:15 ETGQA_T_njecomdb10m_opo01.log -rw-rw-r-- 1 root spadmin 20690620 Jul 3 17:40 event_log
Details on each type of logs and their details will be explained in the next session.
15.1.7.3
Sample contents of Config DIR
This directory has the config related information for all the replication flowing from one site to another. The content of the config file in this DIR is of the following format:
njecomdb10-m:/local/0/opt/splex/ETGQA_S/splex_data/config#more ORA_config datasource:o.SOURCE_SID #source tables splex.demo_src target tables splex.demo_dest routing map target_system@o.target_sid
15.1.7.4
Bin DIR of Shareplex
The path to Bin DIR of Shareplex is given by SP_SYS_BINDIR=/local/0/opt/splex/bin. Sample contents: njecomdb11-m:/local/0/opt/splex/bin#ls -ltr total 33082 -r-sr-x--- 1 root spadmin 345912 Mar 11 2002 qview -r-xr-x--- 1 splexadm spadmin 125896 Mar 11 2002 sp_xport -r-xr-x--- 1 splexadm spadmin 121084 Mar 11 2002 sp_mport -r-xr-x--- 1 splexadm spadmin 85056 Mar 11 2002 que_export -r-sr-x--- 1 root spadmin 126768 Mar 11 2002 dumpque -r-xr-x--- 1 splexadm spadmin 336012 Mar 11 2002 sp_vrfy -r-xr-x--- 1 root spadmin 177168 Mar 11 2002 ora_chpwd -r-sr-x--- 1 root spadmin 1164 Mar 11 2002 ora_cleansp -r-sr-x--- 1 root spadmin 614 Mar 11 2002 cleanup.sql -r-sr-x--- 1 root spadmin 2093 Mar 11 2002 clean_vardir.s -r-xr-x--- 1 splexadm spadmin 63404 Mar 11 2002 ask_redolog_in -r-xr-x--- 1 splexadm spadmin 67428 Mar 11 2002 sp_rev -r-xr-x--- 1 splexadm spadmin 13824 Mar 11 2002 ocap_save_stat -r-xr-x--- 1 splexadm spadmin 3954 Mar 11 2002 Synchronize -r-xr-x--- 1 splexadm spadmin 3815 Mar 11 2002 Restore -r-xr-x--- 1 splexadm spadmin 111068 Mar 11 2002 iwsnmptrap -r-sr-x--- 1 root spadmin 192192 Jun 25 2002 ora_setup -r-xr-x--x 1 splexadm spadmin 563440 Jun 25 2002 sp_ctrl
15.1.7.5
Data Directory of Shareplex
Sample contents: -rw-rw-r-- 1 splexadm spadmin 393 Mar 11 2002 event_table -rw-rw-r-- 1 splexadm opcgrp 0 Jul 10 2002 hints.etgqa_t -rw-r--r-- 1 root spadmin 0 Mar 12 23:02 statusdb.lck -rw-rw-r-- 1 splexadm spadmin 95 Apr 8 14:29 oramsglist -rw-r--r-- 1 splexadm opcgrp 0 Jun 4 16:40 transformation.etgqa_t -rw-rw-r-- 1 splexadm opcgrp 0 Jun 4 16:40 transformation.ETGQA_T -rw-rw-r-- 1 splexadm opcgrp 12638 Jun 4 16:40 conflict_resolution.etgqa -rw-rw-r-- 1 splexadm opcgrp 12638 Jun 4 16:40 conflict_resolution.ETGQA -rw-rw-r-- 1 splexadm opcgrp 3 Jun 4 16:43 actdb -rw-rw-r-- 1 root spadmin 1216 Jul 1 05:41 paramdb -rw------- 1 root spadmin 0 Jul 1 05:41 dirty -rw-rw-r-- 1 root spadmin 0 Jul 1 05:43 services -rw-rw-r-- 1 root spadmin 17990 Jul 1 05:44 statusdb
15.1.7.6
Frequently used/referred files in the data DIR
Statusdb and paramdb. Paramdb is used for the following purpose: njecomdb11-m:/local/0/opt/splex/ETGQA_T/splex_data/data#more paramdb | pg # # User Parameter Database # # This file contains any parameter values which are different from # their default values. The customer may make changes to this file # as they see fit. # # The format of this file is: # # key value pending # desc # # key The unique name for this parameter. It should be of the format # module.name # # value The current value of this parameter. # # pending The pending value of this parameter. This change may take # effect at the next "activate parameters" or reboot. # # desc Description for this parameter. #
15.1.7.7
Contents of oramsglist and its Importance.
njecomdb11-m:/local/0/opt/splex/ETGQA_T/splex_data/data#more oramsglist 20 900 902 908 909 910 911 932 960 1026 1426 1455 1458 1459 1462 1479 1483 1487 1704
2292 2266 This file has the ora error numbers that need to be skipped so that replication can continue, even when these errors are encountered during the process. Important: Note the first line: First line gives the count of the total no: of lines in the file. If at a later point in time, one needs to include another ora error, then first you got to update the count in the first line by one and then include the error number in the end.
15.1.7.8
Notes on some commands/logs Interpretation and Usage
The contents of the event_Log can also be viewed from within Shareplex command line control utility sp_ctrl as follows: Type the following at the Unix prompt having logged in as splexadm at the Unix level, having logged into the right Unix host: Sp_ctrl Type the following at the Shareplex command line control utility prompt: Show log The log DIR has compare logs of the compare jobs that are automated via cron everyday. The compare log dir has the naming convention cmpconfig.mmddyyyy and the files beneath them have the sqls that need to be run, when compare process reports objects out of sync. Out of sync errors are logged in the file report.log. Additionally there are several logs available depending on the processes associated and the jobs run, which will be dealt in detail in the next session. njecomdb11-m:/local/0/opt/splex/ETGQA_T/splex_data/log/cmpconfig.04252003# more report. log ETGQA_T_ECOMMERCE-BH_TRANSMISSION-3221.sql: * 1 out-of-sync row found in this table. This log gives an estimate on the degree of synchronization between the source and the target.
15.1.8 Compare jobs and processes
If Shareplex compare job is running on the host, then you would see Sp_declt and sp_desvr processes running. where declt* client process (on replicate side) *desvr* server process (on primary side)
15.1.9 Information On queues
For information on queues and what commands to be used at the sp_ctrl prompt, refer to the documentation in section 2. Note: The details of the queues are explained in section 2: Some queues are specific to some sites. For eg: one would witness import and post queues only at the replicate sites. Similarly read, capture and export queues would be seen only on the source side, as it sounds logical. With reference to the queue size, there are no restrictions currently set on the size of the file at the OS level and the max size depends on the size allowed for the file, which is OS dependant and specific. With specific reference to commands, following is worth stating. One would issue show capture details at the sp_ctrl prompt at the source side and not at the target, until and unless the target also acts as a source for some other replicate site. Backlog information about the post queues will be more appropriately viewed at the replicate site. Similarly ’Show post detail’ command would be executed from sp_ctrl prompt at the replicate site. Important: Why Shareplex method of replication is considered efficient/preferred is, the analysis of the data change capture is obtained from the redo logs at the OS level instead of at the DB level. The term backlog gives an estimate on the degree/amount of data changes that need to be applied to the target in the form of queue transaction. This could also imply that information may be read from the archive log files at the source and not necessarily from the redo logs depending upon the degree of the backlogs.
15.1.10 Vendor contact details/procedure
Raise a case to the Support group of quest by sending a mail addressed to support@quest.com with the error message and a possible description of the cause and history with a detailed explanation of the then scenario of the system. Shareplex support group is expected to return the response with a case id. The DBA’s are expected to follow-up with the caseid as the key reference number.
15.1.11 Useful commands and hands on approach
The following is the extract from the Conference call regarding SHAREPLEX. PATH variable for SHAREPLEX --> /home/dba/files/SHAREPLEX Login/password: splexadmin/splexadmin_pass After login using the above id/password, "env" gives us the environment setting details for the SHAREPLEX on a given host. ex - as outlined below
njecomdb10-m:/home/splexadm#env _=/bin/env MANPATH=/opt/gnu/man:/export/home/man:/usr/share/man:/usr/local/man LOGIN=splexadm
PATH=/opt/oracle/8.1.7:/opt/oracle/8.1.7/bin:/home/dba/bin:/bin:/opt/bin:/usr/bi n:/usr/sbin:/usr/ccs/bin:/usr/ucb:/usr/lib/X11:/opt/gnu/bin:/usr/openwin:/local/ 0/opt/splex/bin:/local/0/opt/splex/ETGQA_S/splex_data SP_SYS_VARDIR=/local/0/opt/splex/ETGQA_S/splex_data EDITOR=vi LOGNAME=splexadm SP_COP_UPORT=2100 SP_COP_TPORT=2100 TARGET_HOST_NAME=njecomdb11-m ORACLE_SID=ETGQA_S PS1=njecomdb10-m:${PWD}# KRB5CCNAME=FILE:/tmp/krb5cc_p28178 USER=splexadm SHELL=/bin/ksh HOME=/home/splexadm SP_SYS_BINDIR=/local/0/opt/splex/bin TERM=ansi ORACLE_HOME=/opt/oracle/8.1.7 CMASK=022 PWD=/home/splexadm SP_SYS_DIR=/local/0/opt/splex SP_SYS_HOST_NAME=njecomdb10-m TZ=US/Eastern Primary and Target host on which SHAREPLEX is running could be identified in "env" setting variables as SP_SYS_HOST_NAME=njecomdb10-m TARGET_HOST_NAME=njecomdb11-m Scripts in the "cron" to manage Replication sp_logmonitor.sh sp_qstatmonitor.sh qa_splex_compare.sh Location of command file (commands) : /home/dba/files/SHAREPLEX This file outlines information about: 1) 2) 3) 4) 5) Login details to Shareplex. Path location for startup and shutdown of Shareplex. Check queue and status. Check event log. Commands to start and stop queue etc..
executing sp_ctrl at UNIX prompt takes us to command line interface for managing Shareplex sp_ctrl(:2100)> status --> status of Shareplex processes sp_ctrl(:2100)> qstatus --> status of Shareplex queues sp_ctrl(:2100)> show post detail --> Latest transaction that is being applied on the host. sp_ctrl(:2100)> show capture detail sp_ctrl(:2100)> show log
sp_ctrl(:2100)> stop sp_ctrl(:2100)> start Below information could be more helpful at the command line interface List of command classes: connect -- Establish connection defaults run -- Running mode commands config -- Configuration files commands param -- Parameters data base commands stat -- Status inquiries show -- Run time Statistics information event -- Events monitoring and examining info -- Host machine characteristics information set -- Set values misc -- All other commands Type 'help' and a class name for a list of commands in the class Type 'help' and a command name for a full description and syntax.
15.1.12 Q&A Section
How to bring source and target in sync: Get a report of the out of sync tables and the corresponding sync scripts from the log DIR of Shareplex. Be very sure before you can run the scripts as splex oracle user on the target in the replicate site. You ought to be sure of the consequences. More details on this will be updated in next section. How to rectify, object doesn’t exist error in event_Log: Re-activation of the config does help sometimes based on the response from the quest support group.
15.1.13 Troubleshooting Section 15.1.13.1 Problem Message 1
-----Original Message----From: Gryphon tester [mailto:fiamon@lehman.com] Sent: Thursday, July 24, 2003 3:31 PM To: Govindharajulu, Bhavani Subject: FMS-Gryphon:njecomdb11-m: Notice: sp_cnc connecting from njecomdb11-m logon error ORA-00257: archive.. the following information has been picked up from /local/0/opt/splex/ETGQA_T/splex_data/log/event_log
07/24/03 06:00 Notice: sp_cnc (connecting from njecomdb11-m) logon() error(ORA00257: archiver error. Connect internal only, until freed..) detected by 'SPLEX Log Reader' G3 Monitor (njecomdb11-m:pid23606 app_log_reader_splex, v1.1) on 07/24/03 06:01:22 FMS Event(1246923080) njecomdb11-m.SPLEX Log Reader.etgqa.warning.all: Rule(15692) all.all.all.all X SPLEX Log Reader.etgqa.warning.all mail --> dboncall Always filter=1 originated by Gryphon, processed by FMS (njecomdb11-m:pid23610 fmsnotify, v1.39 G3) on 07/24/03 06:01:22 Action Taken Hi Rinnappa, Thank you for the support rendered during off hours. As advised fixed the Shareplex / archiver problem in ETGQA_T. As part of the knowledge sharing process, have included the steps below that was taken to resolve the issue. Problem/Solution: ARCH dir on njecomdb11-m for ETGQA_T was full, due to which ORA-257 error was encountered and post queue was not moving. Logged in as splexadm into njecomdb11-m and issue the following, sp_ctrl> status and qstatus. Note the O/P. First fixed the archiver error as follows: deleted older arch logs from Jun 1-10, having checked the arch log sequence numbers needed based on last successful available backup. Logged in as Oracle on njecomdb11-m-ETGQA_T. svrmgrl> restarted archiver process. (alter system archive log start) Double check. sqlplus system/passwd@ETGQA_T This should work. comment out the cron entries for monitoring #0,20,40 * * * * /local/0/opt/splex/monitors/sp_logmonitor.sh #0,20,40 * * * * /local/0/opt/splex/monitors/sp_qstatmonitor.sh Restart Shareplex on target. Check to see if sp_cop process-2100 port is running. If yes, stop it by issuing, /local/0/opt/splex/scripts/$ORACLE_SID/splex_shut.sh having logged in as splexadm (userid applicable only to QA). -ps –ef | grep sp_cop----check to confirm if process is till running. if yes, log into sp_ctrl>shutdown force. if process still exists, call up/page on call SA to call you back. Take his help in killing the parent sp_cop process (owned by root) meant ONLY FOR QA environment associated with port 2100, BE CAREFUL. You do not have
the right to kill all sp_cop processes in that particular host. Each and every sp_cop process is associated with a particular port number. Pay attention to this point. Startup Shareplex, by issuing /local/0/opt/splex/scripts/$ORACLE_SID/splex_start.sh( as user splexadm). login as sp_ctrl and check for status, qstatus and show post detail. When show post detail is issued, Timestamp against SQL statement as shown below will change Post state should say Apply changes-other states are idle etc..
Last operation posted: Redo log: 4987 Log offset: 34433040 INSERT in "ECOMMERCE"."AUDIT_CT_TRANSACTION" at 07/24/03 04:28:22 Post state : Apply changes
Count of Backlog messages in the post queue should DECREASE. Monitor this change for a few seconds—from sp_ctrl prompt. Initially when the Shareplex is restarted, when status command is issued, import queue may be shown as idle. Wait for sometime. Monitor to see that post queue is running fine. issue status again--import queue status will also be shown as running; (now). Take off the comment in crontab, that you initially placed in crontab (as user splexadm-for QA). 0,20,40 * * * * /local/0/opt/splex/monitors/sp_logmonitor.sh 0,20,40 * * * * /local/0/opt/splex/monitors/sp_qstatmonitor.sh Inform/page the ecommerce end users as per the communication standard set and agreed between Rinnappa and the commerce group. This is considered important by the ecommerce group.
15.1.14 About Shareplex Compare Process and Procedure to Sync out-ofsync tables in Ecommerce Environments
Please sync those tables on ETGSTG_T as splex/splex@ETGSTG_T. After that, please run on njecomdb11-m: splexstg sp_ctrl> compare table ... for each table to make sure they are in sync. Please inform dblair,yosuke,mashah once you are done.
15.1.15 About Quest 15.1.15.1 Software Support Patch notifications
A Patch for Stat, version 4.1.6, has recently been released and is available for download from our website. This Patch fixes a few minor issues previously reported for Stat v4.1.x. To find out what's new in this release please read the release notes available with the download on Support Link. To download this Patch, along with installation instructions and release notes, go to the "Download Software" section of Support Link: http://www.quest.com/support/index.asp
Questions or problems? You can submit them to Technical Support via e-mail to support@quest.com or by creating a case directly in Support Link. Please do not respond directly to this e-mail notification. You can elect to stop receiving product notifications by changing the "Product Notification" setting under Edit User Profile in Support Link.
15.1.15.2
Support
Product: Shareplex for Oracle Oracle : 8.1.7.4 64 bit Shareplex version: 4.0.16.2 Application Version: BEA Web Logic 6.1 Support phone# 949.754.8000 CSI: 1+x1+3201
15.1.16 About Shareplex Production cron entries
njecomdb1-m 0,10,20,30,40,50 * * * * /local/0/opt/splex/monitors/sp_monitor.sh 0 2 * * * /local/0/opt/splex/monitors/sp_cmpmonitor.sh 0 7 * * * /local/0/opt/splex/monitors/sp_cmpproc.sh > /var/tmp/sp_cmpproc.log 2>&1 nyecomdb1-m 0,10,20,30,40,50 * * * * /local/0/opt/splex/monitors/sp_monitor.sh > /dev/null 2>&1 0 5 * * * /local/0/opt/splex/monitors/sp_checkcmp.sh > /var/tmp/cmp.log 2>&1
15.1.17 About Ecommerce User Group Notification Procedures
Change the notification for Shareplex on njecomdb10-m and njecomdb11-m as following: (ETGSTG_S, ETGSTG_T, ETGQA_S, and ETGQA_T replication ) Critical: Page notification during business hours Mail notification during off-hour Warning: Mail notification 24x7
15.1.18 About Script to confirm Shareplex replication status
Attached are the two simple SQLs which can be used to check replication. Please truncate ecommerce.ecom_replication_test table first before running the SQLs. Also commit; everytime you run the SQLs.
15.1.19 About GIS Alert: Patch Application on Production
You need to follow/implement the steps in Section D ONLY at 7:00 P.M EST for patch application in PRODUCTION. Please READ this ALERT completely, before you begin the task. Section A: Background: Source DB: LLPROD (njecomllprod) Target DB: NYLLPROD (nyecomllprod). Section B: Procedure for the patch application(can be adding new tables/sequences etc) is as follows: LLPROD@njecomdb1-m:/local/0/home/dba/files/SHAREPLEX> more addtables 1) DBA (rratanab) copies ecomdb_all.cfg to ecomdb_all_add.cfg (using UNIX cp command) ( /local/0/opt/splex/LLPROD/splex_data/config) sp_ctrl> list config 2) DBA updates ecomdb_all_add.cfg by adding new tables: (using vi editor) 3) DBA runs activate config ecomdb_all_add.cfg on Shareplex LLPROD. sp_ctrl> activate config ecomdb_all_add.cfg sp_ctrl> list config Open another window to see the activation: sp_ctrl> show activate
(at this point old config ecomdb_all.cfg is inactive and ecomdb_all_add.cfg is active) 4) DBA copies ecom_all_add.cfg to ecom_all.cfg DBA runs activate config ecom_all.cfg Note: This step is to preserve the old config name. 5) Add new tables to compare job config /local/0/opt/splex/LLPROD/splex_data/config/ecomdb_cmp.cfg Section C: 1) login as splexprod/passwd into njecomllprod 2) cd $SP_SYS_VARDIR/config 3) pwd should show the following splexprod@njecomdb1-m:/local/0/opt/splex/LLPROD/splex_data/config 1) cp ecomdb_all.cfg ecomdb_all.cfg.03Sep03 2) cp ecomdb_all.cfg ecomdb_all_new.cfg 3) ADD the following lines at the end of the file ecomdb_all_new.cfg ECOMMERCE.NAV_CATEGORY ECOMMERCE.NAV_CATEGORY nyecomllprod@o.NYLLPROD ECOMMERCE.NAV_ENTRY_POINT ECOMMERCE.NAV_ENTRY_POINT nyecomllprod@o.NYLLPROD ECOMMERCE.NAV_SUBCATEGORY ECOMMERCE.NAV_SUBCATEGORY nyecomllprod@o.NYLLPROD ECOMMERCE.NAV_URI ECOMMERCE.NAV_URI nyecomllprod@o.NYLLPROD ECOMMERCE.NAV_DIVISION ECOMMERCE.NAV_DIVISION nyecomllprod@o.NYLLPROD ECOMMERCE.NAV_LOCATION ECOMMERCE.NAV_LOCATION nyecomllprod@o.NYLLPROD ECOMMERCE.NAV_SUBDIVISION ECOMMERCE.NAV_SUBDIVISION nyecomllprod@o.NYLLPROD ECOMMERCE.AUDIT_NAV_CATEGORY ECOMMERCE.AUDIT_NAV_CATEGORY nyecomllprod@o.NYLLPROD ECOMMERCE.AUDIT_NAV_ENTRY_POINT ECOMMERCE.AUDIT_NAV_ENTRY_POINT nyecomllprod@o.NYLLPROD ECOMMERCE.AUDIT_NAV_SUBCATEGORY ECOMMERCE.AUDIT_NAV_SUBCATEGORY nyecomllprod@o.NYLLPROD ECOMMERCE.AUDIT_NAV_URI ECOMMERCE.AUDIT_NAV_URI nyecomllprod@o.NYLLPROD ECOMMERCE.AUDIT_NAV_DIVISION ECOMMERCE.AUDIT_NAV_DIVISION nyecomllprod@o.NYLLPROD ECOMMERCE.AUDIT_NAV_LOCATION ECOMMERCE.AUDIT_NAV_LOCATION nyecomllprod@o.NYLLPROD ECOMMERCE.AUDIT_NAV_SUBDIVISION ECOMMERCE.AUDIT_NAV_SUBDIVISION njecomdb11@o.NYLLPROD ECOMMERCE.CATEGORY_ID_SEQ ECOMMERCE.CATEGORY_ID_SEQ nyecomllprod@o.NYLLPROD ECOMMERCE.DIVISION_ID_SEQ ECOMMERCE.DIVISION_ID_SEQ nyecomllprod@o.NYLLPROD ECOMMERCE.ENTRY_POINT_ID_SEQ ECOMMERCE.ENTRY_POINT_ID_SEQ nyecomllprod@o.NYLLPROD ECOMMERCE.NAV_LOCATION_ID_SEQ ECOMMERCE.NAV_LOCATION_ID_SEQ nyecomllprod@o.NYLLPROD ECOMMERCE.NAV_URI_ID_SEQ ECOMMERCE.NAV_URI_ID_SEQ nyecomllprod@o.NYLLPROD ECOMMERCE.SUB_CATEGORY_ID_SEQ ECOMMERCE.SUB_CATEGORY_ID_SEQ nyecomllprod@o.NYLLPROD ECOMMERCE.SUB_DIVISION_ID_SEQ ECOMMERCE.SUB_DIVISION_ID_SEQ nyecomllprod@o.NYLLPROD
Section D: Implementation: Ground work: LLPROD Autosys backup job(DBANPORA_LLPROD ) should be complete and successful before the following patch application can commence. This is important. LLPROD backup will complete by about 7:00 P.M EST. Please monitor the backup process closely and keep khaled informed if there is a delay. What is important is, we should have a good base rman dump of LLPROD PROD DB , before the patch application. Page krahman at 7:00 P.M EST-Sep 03, 2003 btell krahman "Are we ready for patch application in PROD. Shall I activate config in LPROD now--thanks-Srinivas-- ora_dba -13256" (As part of process, we check with him to confirm that data will not flow into the new tables in the source, for which replication is yet to be enabled.) btell krahman "Please confirm if the new tables are created in source & target in PROD(LLPROD/NYLLPROD)--ThanksSrinivas--ora_dba-13256" On receiving confirmation from Khaled(krahman), do the following: login into njecomllprod as splexprod/ i. check replication queues on both side njecomllprod and nyecomllprod. All queues should be up running. sp_ctrl > status 5) sp_ctrl > activate config ecomdb_all_new.cfg 6) Open another telnet session having logged in as splexprod on njecomllprod. 7) sp_ctrl> 8) sp_ctrl> list config 9) sp_ctrl >show activate (at this point old config is inactive and new config is active) (You should see the new config name ecomdb_all_new.cfg as ACTIVE). 10) exit 11) At the OS level(you should still be logged in with id--splexprod on njecomllprod) 12) cp ecomdb_all_new.cfg ecomdb_all.cfg 13) sp_ctrl 14) sp_ctrl> activate config ecomdb_all.cfg (Step #14 is to preserve the old config file name) Check to see that activation is complete. 15) check replication queues on both side njecomllprod and nyecomllprod. All queues should be up running. sp_ctrl > status Page krahman , once the above task is done/complete. btell krahman "New config has been activated in PROD LLPROD. Please check for replication--data flow--thanks-Srinivas- 13256-ora_dba". 1) 2) 3) 4)
15.1.20 About few tips on checking if activation of config is complete
Four things to check if activate config is fine: sp_ctrl (njecomdb10-m:2100)> show log sp_ctrl (njecomdb10-m:2100)> list config sp_ctrl (njecomdb11-m:2100)> status sp_ctrl (njecomdb11-m:2100)> qstatus
You should see post queue running and post queue info from "qstatus". Of course, the first command would be to run show config in another window, when activation is in progress, to view the progress of the configuration.
15.2
Installation of Binaries?
The Detailed description of the Installation of Oracle Binaries has been covered under the Sections 3.1 and 3.2 which are under the main section 3.0
15.3 Replication Set up?
15.4
Ecomm Release Work plan for Production, Staging and QA
At Lehman Brothers the Replication setup is being done by Rinappa. Till Date
15.5 Logging a case with quest
For logging a case with Quest, one needs to register with quest support and should have quest support email contact which is support@quest.com
15.6 How to resolve: STAGE Shareplex compare job reported the following tables are out-of-sync
Please note that the Host names and database names used in this doc are for Staging. Please change them appropriately according to the server you are working. Case: Resolve Out of sync issues on Ecommerce STAGE environment (ETGSTG_S/T), due to Shareplex data replication flowing from source to target. Shareplex login for STAGE: splexstg/ Host details: Source: njecomdb10-m(ETGSTG_S)
Target:
njecomdb11-m(ETGSTG_T),
Environment details: Source: njecomdb10-m:/home/splexstg# env |grep ORA ORACLE_SID= ETGSTG_S ORACLE_HOME= /opt/oracle/8.1.7 Target: njecomdb11-m:/local/0/opt/splex/ETGSTG_T/splex_data/log# env |grep ORA ORACLE_SID= ETGSTG_T ORACLE_HOME= /opt/oracle/8.1.7 The Steps to be followed are as follows: 1 2 Login to the target njecomdb11-m as splexstg/splexstg_pass. cd $SP_SYS_VARDIR/log/cmpconfig.$date.. Here we find the .sql files that reported out-of-sync (Pay particular attention here). Note the complete path of the .sql files that are reported as out-of-sync. You can get these names from the alert that generated/mailed to Ora_Dba group as a result of the scheduled cron job that runs for all ecommerce Shareplex environments. $sqlplus splex/splex@ETGSTG_T @ETGSTG_T_ECOMMERCE-EUSER_LOGIN_COUNT-16807.sql After running all the sql files for the reported out-of-sync tables in the target DB as mentioned above, issue a commit in the session and exitHow to run Compare job from the source environment: FYI-Compare job is always run from the source environment where replication data flow begins. Login to njecomdb10-m: njecomdb10-m:/home/splexstg#sp_ctrl sp_ctrl (njecomdb10-m:2200)>> compare table ecommerce. to ecommerce. target njecomdb11-m@o.ETGSTG_T sp_ctrl (njecomdb10-m:2200)>> exit If the table against which the compare job is run is small, then the process may complete soon, else it may take a longer time. The following command should help you check if the compare process is still running on the source server. njecomdb10-m:/home/splexstg#ps -ef |grep des Having run the compare job in the source environment as said, log into the target environment to check for the log and the sql files in the DIRs as mentioned below, to gain an inference on the job done. Login to njecomdb11-m: njecomdb11-m:/home/splexstg#cd $SP_SYS_VARDIR/log The logs for the compare process will be found in the target environment in the following directory.
3 4
5 6
7
8
9 10
njecomdb11-m:/local/0/opt/splex/ETGSTG_T/splex_data/log# ls ETGSTG_T_declt*.log (Log files associated to the tables sync'd are found).
-ltr
NOTE: View the log files only after making sure that the compare process on the source has completed. View the associated sql files generated on the target host and check to see if no rows are out of sync and that there are no sql statements(update) provided to get the tables in sync… This means, if you run ps -ef| grep des* on the source environment (njecomdb10m), then it should not return anything. The log files should show that "exited without errors" for each table that is sync'd Yet another word of reminder with reference to sync procedure and points to note: In such reported out-of-sync scenarios as the one reported below, ( as said before), we run the compare process on the source host via sp_ctrl for the specific tables. The reports for the compare process are generated as logs/sqls etc on the target host. Once we successfully sync the tables by running the appropriate sql scripts on the target, do remember to remove the specific and the pertinent compare logs generated on the target under the Shareplex log DIR. These logs reside directly under the log DIR and not under any specific folders(like the ones that we have for the scheduled compare process via cron). Point is: IF YOU RUN THE COMPARE PROCESS MANUALLY, then once syncing is done, remove the respective logs alone from the target host. IF THE LOGS GENERATED ON THE TARGET HOST IS the outcome of a scheduled run via cron, then you will find them under specific folders like cmpconfig.$date... Please leave these logs /folder intact, since this will help us track back, in case of problems.
15.7
How to kill only Compare Shareplex process on the Source System
njecomdb10-m: /home/splexstg# sp_ctrl sp_ctrl (njecomdb10-m:2200)> status Brief Status for njecomdb10-m Process Cop 03:46:46 Read 11:02:54 Export 16:14:58 Export 03:46:47 State Running Running Running Running PID 1992 5451 463 2045 Running Since 10-Jun-04 27-Jun-04 10-Jun-04 10-Jun-04
Post 03:46:47 Import 16:12:49 Capture 18:05:03 Compare Svr 06:40:54 Cmd & Ctrl 09:55:59
Running Running Running Running Running
2044 73 13599 6115 29533
10-Jun-04 10-Jun-04 18-Jun-04 25-Jun-04 29-Jun-04
ETGSTG_S@njecomdb10-m:/local/oracle_db_dump2/ETGSTG_S/backup> ps -ef|grep sp_des oracle 29523 26839 0 09:55:45 pts/6 0:00 grep sp_des root 6115 1992 12 Jun 25 ? 5499:11 /local/splex/bin/sp_desvr table ECOMMERCE.AUDIT_EUS ER_PROFILE to ECOMMERCE.AUDI What do we need to note:? Note the number 1992 is a parent process !! njecomdb10-m:/home/splexstg# ps -ef|grep 1992 root 2044 1992 0 Jun 10 ? 0:01 /local/splex/bin/sp_opst -u2200 root 1992 1 0 Jun 10 ? 1:50 /local/0/opt/splex/bin/sp_cop -u2200 root 2045 1992 0 Jun 10 ? 52:53 /local/splex/bin/sp_xport -u2200 root 13599 1992 0 Jun 18 ? 235:38 /local/splex/bin/sp_ocap -u2200 root 5451 1992 0 Jun 27 ? 16:57 /local/splex/bin/sp_ordr -u2200 splexstg 29639 29524 0 09:56:20 pts/6 0:00 grep 1992 root 73 1992 0 Jun 10 ? 0:02 /local/splex/bin/sp_mport 0x0a05a4b4+PI+njecomdb11-m+ sp_mport+0x0a05a43f R -u22 root 463 1992 0 Jun 10 ? 62:48 /local/splex/bin/sp_xport -u2200 root 6115 1992 13 Jun 25 ? 5499:45 /local/splex/bin/sp_desvr table ECOMMERCE.AUDIT_EUS ER_PROFILE to ECOMMERCE.AUDI
15.8
What request should be placed to SA to kill the process owned by root?
Here is an example of the Request to be place to SA: Please kill ONLY process id 6115 owned by root on njecomdb10-m. Please call me @ Extn for any Questions. Thanks and Regards, DBA Contact number
Wrong Example of Request to SA !!! What we should NOT DO? Please kill the following process owned by root ETGSTG_S@njecomdb10-m:/local/oracle_db_dump2/ETGSTG_S/backup> ps -ef|grep sp_des oracle 29523 26839 0 09:55:45 pts/6 0:00 grep sp_des root 6115 1992 12 Jun 25 ? 5499:11 /local/splex/bin/sp_desvr table ECOMMERCE.AUDIT_EUS ER_PROFILE to ECOMMERCE.AUDI WHY? You will BRING DOWN SP_COP TOO.
16
Troubleshooting
16.1 When ORA-600/ ORA-7445 Error occurs
Whenever ORA-600 or ORA-7445 error occurs ,need to do the following Verify if the Error has affected the functioning of the Dataabase , because sometimes the error causes the database to crash Check the SQL /process running at the time of the error and also verify what kind of actvitity is being done through the SQ lie INSERT/UPDATE/DELETE As the 7445 error can occur even when the user terminates the Process initiated by him midway without completing the process. Verify the Database Alert log and also the Trace file generated ,to investigate further about the error , if the error is caused by some User Process then please inform the user about the same
16.2
When 1652 Error occurs/ Tablespace Fills up
The !652 Erro can come for various reasons The tablespace getting filled up due to fragmentation/Bulk processing by User Dut to Lack of space on the tablespace Dba can coalesce the tablespace to release contigous space Alter tablespace coalesce; If the tablespace is full then the DBA can resize the datafile in the tablespace and add space or add an additional datafile to resolve the issue. Alter database add datafile „ /local/oracle//oradata1/x.dbf‟ resize 3000M; Else Alter tablespace X Add datafile „ /local/oracle//oradata1/x.dbf ‟ size 1000M;
16.3
When archive log dest fills up
The archive log destination fiiling up is a very critical error and has to be resolved immediately to avoid database hanging problem or cannot connect to the database issue. Whenever the above error occurs, DBA needs to free up the space on the archive destination either by moving the archive logs to the backup directory or by deleting the old logs. The dba needs to set up a cron job or an autosys job to delete the unwanted olg logs, gzip the archivelogs and if necessary move the same to the backup directory. Once the above is done the Dba needs to do the following Alter system archive log start; Alter system switch logfile Alter system checkpoint; And verify the generation of the archive logs in the database.
16.4
When database hangs
When the Database hangs , the first thing that the DBA should look is the space in archive log destination, if this is full the database hangs, so DBA needs to follow the process described in section 16.3 Else the Dba has to check the process running on the database joining the V$ tables v$Process and v$session and for the SQL „s running . Also check whether any lock‟s are present which are blocking the resources. Inform the Users about the problem , Keep the users in the loop Always
16.5 When Host/ Database crashes
When the Database crashes the following has to be done/taken care The DBA need to know whether the database /host was under VCS , if the Database was under VCS then the DBA has to coordinate with the SA to clear the VCS fault and also to bring up the Database and the listener through VCS The DBA needs to inform the User community about the crash and the time line for the issue to be fixed . The DBA should Analyze the Database alert log and also the trace files generated at the time of the database crash Also get the SQL‟s running for that session , check the Oracle Support site for possible solutions and also if necessary raise a TAR to oracle and post all the details and upload the alert log and trace file for investigations. And solution
16.6 Recovering from Deleted data (Flashback / Logminer)
17
Tips
17.1 Consolidated procedure to login to all different type of hosts
17.1.1 DBU hosts (i.e. nydbupora0-m, nydbupora1-m)
Login as Oracle on DBU hosts (i.e. nydbupora0-m,nydbupora1-m). We can use the kinit procedure or we can use the rsh using your id to loging to dbu host. Basic objective is that everyone should know the procedure to login to hosts.
17.1.2 Login to any box (or bo-xsrv)
1 2 3 1 2 3 4 Login to any box (or bo-xsrv) using your id. rsh to dbu host and it will work fine because our unix id is having the permission for SSH. su - oracle to loging to oracle. Or Login to the usual box you login, with your own id and password. Execute /opt/krb5/bin/kinit (give your own password). ssh (give your own password). su - oracle (give oracle password here).
17.1.3 On HR hosts (i.e. njhrprd1)
1 2 3 Telnet to the hosts. Login as your user id. su – oracle
17.1.4 On the hosts across firewall (i.e. njovmlmprd1, nyovmlmprd2)
1 2 3 Login to the usual box you login, with your own id and password. Execute /opt/krb5/bin/kinit (give oracle password.) kinit -A oracle /proxy-rlogin.pl njovmlmprd1 -l oracle –x
17.2
How to Read Core Files
COND03@njoradev1:/home/dba/bin> strings -a core | grep SID full_list="`ls -1t /local/oracle/$ORACLE_SID/arch/*log* | grep -v gz | tail +3`" =ls -1t /local/oracle/$ORACLE_SID/arch/*log* | grep -v gz | tail +3 /local/oracle/$ORACLE_SID/arch/*log* DORACLE_SID ${ORACLE_SID} /tmp/${ORACLE_SID}.instance.$$.check Up=`cat /tmp/${ORACLE_SID}.instance.$$.check | grep ${ORACLE_SID} | awk ' { print Up=`cat /tmp/${ORACLE_SID}.instance.$$.check | grep ${ORACLE_SID} | awk ' { print $1 } '` "${ORACLE_SID}" /tmp/${ORACLE_SID}.instance.$$.check /tmp/${ORACLE_SID}.instance.$$.check /tmp/${ORACLE_SID}.instance.$$.check cal/oracle/$ORACLE_SID/arch/*log* "
#/home/technoids/bin/gzip /local/oracle/$ORACLE_SID/arch/*log* & running=`/bin/ps -aef | grep OraBackup | grep $ORACLE_SID | grep -v grep` echo "$ORACLE_SID: Log Archived at: `date`" full_list="`ls -1t /local/oracle/$ORACLE_SID/arch/*log* | grep -v gz | tail +3`" COND03@njoradev1:/home/dba/bin> strings -a core | grep ORACLE_HOME eORACLE_HOME $ORACLE_HOME/bin/sqlplus $ORACLE_HOME/bin/sqlplus COND03@njoradev1:/home/dba/bin> strings -a core | grep pmon COND03@njoradev1:/home/dba/bin> strings -a core | grep DB COND03@njoradev1:/home/dba/bin> strings -a core | grep oracle /opt/oracle/9.2.0.5 /home/oracle oracle full_list="`ls -1t /local/oracle/$ORACLE_SID/arch/*log* | grep -v gz | tail +3`" =ls -1t /local/oracle/$ORACLE_SID/arch/*log* | grep -v gz | tail +3 /local/oracle/$ORACLE_SID/arch/*log* cal/oracle/$ORACLE_SID/arch/*log* " #/home/technoids/bin/gzip /local/oracle/$ORACLE_SID/arch/*log* & full_list="`ls -1t /local/oracle/$ORACLE_SID/arch/*log* | grep -v gz | tail +3`" HOME=/home/oracle LOGNAME=oracle COND03@njoradev1:/home/dba/bin> strings -a core | grep ver server backtrack stack overflow: expression generates too many alternatives /var/nis/.pref_servers /var/nis/.pref_servers Active servers: Active servers: Failed to load the new preferred server info. Reusing current list. /var/nis/.pref_servers /var/nis/.pref_servers /var/nis/.pref_servers can't bind to a server which serves domain no such map in server's domain internal yp server or client error yp client/server version mismatch NIS+ servers unreachable Server busy, try again Name not served by this server Server out of memory Not master server for this domain Passed object is not the same object on server Unable to authenticate NIS+ server No file space on server Unable to create process on server Master server busy, full dump rescheduled.
mp_invert _lib_version .SUNW_version .SUNW_version .SUNW_versym COND03@njoradev1:/home/dba/bin> strings -a core | grep mem Out of memory out of memory xdr_array: out of memory xdr_reference: out of memory out of memory parse_info: out of memory parse_info: out of memory get_line: out of memory get_line: out of memory out of memory out of memory out of memory Server out of memory COND03@njoradev1:/home/dba/bin> strings -a core |grep tns COND03@njoradev1:/home/dba/bin> strings -a core |grep hostname loadLocalFile: gethostname failed [errno=%d] gethostname() failed [errno=%d] hostname COND03@njoradev1:/home/dba/bin> strings -a core |grep SID full_list="`ls -1t /local/oracle/$ORACLE_SID/arch/*log* | grep -v gz | tail +3`" =ls -1t /local/oracle/$ORACLE_SID/arch/*log* | grep -v gz | tail +3 /local/oracle/$ORACLE_SID/arch/*log* DORACLE_SID ${ORACLE_SID} /tmp/${ORACLE_SID}.instance.$$.check Up=`cat /tmp/${ORACLE_SID}.instance.$$.check | grep ${ORACLE_SID} | awk ' { print Up=`cat /tmp/${ORACLE_SID}.instance.$$.check | grep ${ORACLE_SID} | awk ' { print $1 } '` "${ORACLE_SID}" /tmp/${ORACLE_SID}.instance.$$.check /tmp/${ORACLE_SID}.instance.$$.check /tmp/${ORACLE_SID}.instance.$$.check cal/oracle/$ORACLE_SID/arch/*log* " #/home/technoids/bin/gzip /local/oracle/$ORACLE_SID/arch/*log* & running=`/bin/ps -aef | grep OraBackup | grep $ORACLE_SID | grep -v grep` echo "$ORACLE_SID: Log Archived at: `date`" full_list="`ls -1t /local/oracle/$ORACLE_SID/arch/*log* | grep -v gz | tail +3`" DBReindex.ksh -S CAMEOQA -i CAMEO_include.lst -n N -m ora_dba@lehman.com
17.3
1 2 Access SQL*Plus.
How to generate explain for SQLs
SQL> EXPLAIN plan set statement_id = 'your_tar_number' FOR The message 'Explained' is returned. SQL> SELECT LPAD(' ', 2*(LEVEL-1))||operation||' '||options||' '|| object_name ||' '|| decode(id,0, 'Cost = ' ||position) "Query Plan " FROM plan_table Start with id = 0 and statement_id = 'your_tar_number' Connect by prior id = parent_id and statement_id = 'your_tar_number'; The output should be as follows: Query Plan SELECT STATEMENT Cost = TABLE ACCESS FULL If you replace your Select statement with the above 'SELECT sysdate FROM dual' statement, you will get the Explain Plan for your Select statement. You must also have a PLAN_TABLE in your schema. If one does not exist, you must create it. To create PLAN_TABLE, you can execute the script: $ORACLE_HOME/rdbms/admin/utlxplan.sql.
3
4
5
17.4
Explain plan Add on
Cleanup and Re-run explain plan using same Statement_id: SQL> select distinct object_name, statement_id, cost from plan_table where STATEMENT_ID='BHAVANI'; OBJECT_NAME STATEMENT_ID COST ------------------------------ ------------------------------ ---------ALERTS_MESSAGE BHAVANI 1623 BHAVANI 3412 BHAVANI 5362 BHAVANI 1760851 BHAVANI SQL> delete from plan_table where STATEMENT_ID='BHAVANI'; 8 rows deleted. SQL> COMMIT; Commit complete. SQL> select distinct object_name, statement_id, cost from plan_table where STATEMENT_ID='BHAVANI';
no rows selected SQL> EXPLAIN plan set statement_id = 'BHAVANI' FOR SELECT * FROM ESERVICES.alerts_message a WHERE expiration_timestamp >= SYSDATE AND subject_id IN (SELECT DISTINCT subject_id FROM ESERVICES.alerts_message WHERE expiration_timestamp >= SYSDATE) AND expiration_timestamp IN (SELECT max(b.expiration_timestamp) FROM ESERVICES.alerts_message b WHERE a.subject_id = b.subject_id) / Explained. SQL> @/home/dba/sql/get_plan_for_sql.sql Enter value for statement_id: BHAVANI old 5: Start with id = 0 and statement_id = '&STATEMENT_ID' new 5: Start with id = 0 and statement_id = 'BHAVANI' Enter value for statement_id: BHAVANI old 6: Connect by prior id = parent_id and statement_id = '&STATEMENT_ID' new 6: Connect by prior id = parent_id and statement_id = 'BHAVANI' Query Plan SELECT STATEMENT Cost = 1666196 FILTER SORT GROUP BY HASH JOIN HASH JOIN SEMI TABLE ACCESS FULL ALERTS_MESSAGE TABLE ACCESS FULL ALERTS_MESSAGE TABLE ACCESS FULL ALERTS_MESSAGE 7 rows selected.
17.5
To check memory used by pinned SQLs
select sum(SHARABLE_MEM/(1024*1024)) from v$db_object_cache; select sum(SHARABLE_MEM/(1024*1024)) from v$db_object_cache where KEPT = 'YES';
17.6
Character set
AL32UTF8 This is the UTF-8 encoded character set introduced in Oracle9i. AL32UTF8 is the database character set that supports the latest version (3.2 in 10g) of the Unicode standard. It also provides support for the newly defined supplementary characters. All supplementary characters are stored as 4 bytes. AL32UTF8 was introduced because when UTF8 was designed (in the times of Oracle8) there was no concept of supplementary characters, therefore UTF8 has a maximum of 3 bytes per character. Changing the design of UTF8 would break backward compatibility, so a new character set was introduced. The introduction of surrogate pairs should mean that no significant
architecture changes are needed in future versions of the Unicode standard, so the plan is to keep enhancing AL32UTF8 as necessary to support future version of the Unicode standard, for example work is now underway to make sure we support Unicode 4.0 in AL32UTF8 in the release after 10.1. UTF8 is a varying width 1-3 bytes per character Unicode encoding. It issupported for both database and national character sets. It is a binary superset of US7ASCII. UTF8 corresponds to Unicode CESU-8 encoding. Differences between UTF8 vs AL16UTF16 vs AL32UTF8 AL16UTF16 is fixed-width 2 bytes per character. It is supported for NCHAR,NVARCHAR2 and NCLOB only (national character set). It is not a binary superset of any other Oracle character set. AL16UTF16 corresponds to Unicode UTF-16 encoding. AL32UTF8 is a varying width 1-4 bytes per character. It is supported forCHAR, VARCHAR2, LONG and CLOB only (database character set). It is a binary superset of UTF8 (in 9.2 only) and US7ASCII. AL32UTF8 corresponds to Unicode UTF-8 encoding. Differences between UTF8 and AL32UTF8 UTF8 stores Unicode characters with code points > U+FFFF as two surrogate characters, three bytes each AL32UTF8 stores Unicode characters with code points > U+FFFF as one four-byte character UTF8 will not be updated anymore when new Unicode versions are released,only AL32UTF8 and AL16UTF16 will. Due to compatibility problems with pre-9i versions use UTF8 if you have Oracle8(i) clients connecting to the database. Use AL32UTF8 in pure Oracle9i environment.
17.7
Use LogMiner to Extract SQL Redo for DML Against Dropped Table
PURPOSE This section discusses and demonstrates how to locate and analyze DML (INSERT, UPDATE, DELETE) on a dropped non-partitioned table (DROP TABLE) in online and archived redo logs using LogMiner. This approach will not work with RANGE, HASH or Composite partitioned tables. For LogMiner basics, refer to the Oracle8i Administrator's Guide and Supplied Packages Reference for an introduction to LogMiner and for general instructions. SCOPE & APPLICATION This section assumes the reader has basic understanding of fixed tables and LogMiner. This approach does not guarantee that a dropped table can be fully reconstructed from mining archive logs. Although INSERT and DELETE statements are straightforward to extract, UPDATE can only be accurately mined if the original DML SET-clause updates all columns participating in a UNIQUE constraint (Primary Key or otherwise). The redo records generated for UPDATE statements in Oracle8/8i use ROWID to identify the updated row. Oracle9i will provide the framework for UPDATE statement reconstruction in LogMiner without any ROWID references based on additional column data added to the Oracle9i redo log stream for UPDATE statements only.
This approach will only work if the exact structure (column order and datatype) of the dropped table is known. This article assumes that a LogMiner dictionary which contains the data dictionary details regarding the dropped table does NOT exist. If such a LogMiner dictionary exists, then reference the 'Summary' section given below for redo/undo statement analysis and usage. The object name of the new object intentionally does not match the dropped object name in the following example. This approach requires a data merge step, but this is deemed far safer. In Oracle8i, LogMiner can not be used to obtain redo/undo information about DML operations on index-organized tables (IOTs), clustered tables/indexes, chained rows and non-native data types. Refer to the following note for LogMiner analysis of DML on chained rows: LogMiner Analysis of DML on Chained Rows. Refer to the Oracle 8i Administrator's Guide "Managing Archived Redo Logs" for additional information about usage and restrictions. Summary: Without a dictionary file, the SQL statements generated by LogMiner will use Oracle internal object IDs for the object name, column positions instead of column names, and present column values as HEX data. Thus, assuming that a LogMiner dictionary does not exist which contains the dropped table (object_id A), a new table (object_id B) that exactly matches the column name, order and datatype of the dropped object will be used to build a LogMiner dictionary that will be used in interpreting redo logs for the dropped object. Before analyzing the redo logs (dbms_logmnr.start_logmnr), all object references will be changed from 'B' to 'A' in the LogMiner dictionary. The LogMiner dictionary is 100% self-contained and does not impact the Data Dictionary of the instance. Example: Setup: Create table and generate redo (INSERT, UPDATE, DELETE) SQL> connect test/test Connected. SQL> create table t1 (col1 int); Table created. SQL> -- T1 has an object_id = 24980 SQL> select * from dba_objects where object_name = 'T1'; OWNER -----------------------------OBJECT_NAME -----------------------------------------------------------------------------SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- -----------------CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S ------------------ ------------------ ------------------- ------- - - TEST T1 24980 24980 TABLE
07-JUN-01 18:35:47 07-JUN-01 18:35:47 2001-06-07:18:35:47 VALID N N N SQL> alter system switch logfile; System altered. SQL> -- Current archive log sequence number SQL> select sequence# from v$log where status = 'CURRENT'; SEQUENCE# ---------26 SQL> -- Perform DML on original object. SQL> insert into t1 values (1); 1 row created. SQL> insert into t1 values (2); 1 row created. SQL> update t1 set col1 = 3 where col1 = 1; 1 row updated. SQL> delete from t1 where col1 = 2; 1 row deleted. SQL> commit; Commit complete. SQL> -- Switch archive log to isololate DML SQL> alter system switch logfile; System altered. Drop table T1 (unintentional) SQL> drop table t1; Table dropped. Create table T2 (structure identical to T1). SQL> connect test/test Connected. SQL> create table t2 (col1 int);
Table created. SQL> -- T2 has an object_id = 24981 SQL> select * from dba_objects where object_name in ('T2'); OWNER -----------------------------OBJECT_NAME -----------------------------------------------------------------------------SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- -----------------CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S ------------------ ------------------ ------------------- ------- - - TEST T2 24981 24981 TABLE 07-JUN-01 18:40:15 07-JUN-01 18:40:15 2001-06-07:18:40:15 VALID N N N Build LogMiner Dictionary which is only aware of T2. SQL> connect internal Connected. SQL> -- Directory which is permitted for pl/sql file i/o. SQL> select value from v$parameter where name = 'utl_file_dir'; VALUE ------------------------------------------------------------------------------/database/V816/extproc SQL> -- Build LogMiner Dictionary (T2 only) SQL> begin 2 sys.dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', 3 dictionary_location => '/database/V816/extproc'); 4 end; 5 / PL/SQL procedure successfully completed. Study v$logminer_contents to isolate object id of DROP TABLE. SQL> connect internal Connected. SQL> -- Add log that contains DML on T1 (#26) SQL> begin 2 dbms_logmnr.add_logfile ( 3 LogFileName =>
4 5 6 7
'/database/V816/archivelogs/V816_ArchiveLog_1_0000000026.dbf', Options => dbms_logmnr.new); end; /
PL/SQL procedure successfully completed. SQL> -- Begin LogMiner session SQL> begin 2 dbms_logmnr.start_logmnr ( 3 dictFileName => '/database/V816/extproc/dictionary.ora'); 4 end; 5 / PL/SQL procedure successfully completed. SQL> -- Attempt to identify object id of dropped table SQL> select distinct data_obj# from v$logmnr_contents 2 minus 3 select object_id from dba_objects; DATA_OBJ# ---------0 24980 SQL> -- Since object 24980 does not exist in LogMiner Dictionary SQL> -- unable to translate information or provide segment name SQL> SQL> select seg_name, sql_redo from v$logmnr_contents 2 where data_obj# = 24980; SEG_NAME -------------------------------SQL_REDO -----------------------------------------------------------------------------insert into UNKNOWN.Objn:24980(Col[1]) values (HEXTORAW('c102')); insert into UNKNOWN.Objn:24980(Col[1]) values (HEXTORAW('c103')); update UNKNOWN.Objn:24980 set Col[1] = HEXTORAW('c104') where ROWID = 'AAAGGUAAOAAA/8eAAA'; delete from UNKNOWN.Objn:24980 where Col[1] = HEXTORAW('c103') and ROWID ='AAAGGUAAOAAA/8eAAB'; Edit LogMiner Dictionary to modify object id. (Do NOT change DICTIONARY_TABLE (high-water mark for object id).) Suggestion: Use operating system copy of dictionary.ora prior to modifications. These changes ONLY impact LogMiner Dictionary not Oracle Data Dictionary.
Entire LogMiner Dictionary not included. *** DO NOT CHANGE DICTIONARY_TABLE (example): INSERT_INTO DICTIONARY_TABLE VALUES ('V816',3170452609,'01/18/2001 11:31:13','06/07/200118:46:25',,,5966585593957,'05/31/2001 11:02:11','01/18/2001 11:31:13','REDODATA','8.1.6.0.0','UTF8','8.1.6.1.0','Production',24981,22538); For every other occurance of 'new' object id (ex. 24981) change to old object id (ex. 24980): --- Original INSERT_INTO OBJ$_TABLE VALUES (24981,24981,82,'T2',1,'',2,to_date('06/07/2001 18 :40:15', 'MM/DD/YYYY HH24:MI:SS'),to_date('06/07/2001 18:40:15', 'MM/DD/YYYYHH24:MI:SS'),to_date('06/07/2001 18:40:15', 'MM/DD/YYYY HH24:MI:SS'),1,'','',0,,6,,,'','', ); INSERT_INTO TAB$_TABLE VALUES (24981,24981,11,14,261917,,,1,,10,40,1,255,1,'---INSERT_INTO COL$_TABLE VALUES (24981,1,1,22,0,'COL1',2,22,0,,0,0,,'',1,0,0,0,0,0 ,,'','', ); --- Update INSERT_INTO OBJ$_TABLE VALUES (24980,24980,82,'T2',1,'',2,to_date('06/07/2001 18:40:15', 'MM/DD/YYYY HH24:MI:SS'),to_date('06/07/2001 18:40:15', 'MM/DD/YYYYHH2 4:MI:SS'),to_date('06/07/2001 18:40:15', 'MM/DD/YYYYHH24:MI:SS'),1,'','',0,,6,, ,'','', ); INSERT_INTO TAB$_TABLE VALUES (24980,24980,11,14,261917,,,1,,10,40,1,255,1,'---INSERT_INTO COL$_TABLE VALUES (24980,1,1,22,0,'COL1',2,22,0,,0,0,,'',1,0,0,0,0,0,,'','', ); Mine redo log for DML. SQL> connect internal Connected. SQL> -- Add log that contains DML on T1 (#26) SQL> begin 2 dbms_logmnr.add_logfile ( 3 LogFileName => 4 '/database/V816/archivelogs/V816_ArchiveLog_1_0000000026.dbf', 5 Options => dbms_logmnr.new);
6 end; 7 / PL/SQL procedure successfully completed. SQL> -- Begin LogMiner session SQL> begin 2 dbms_logmnr.start_logmnr ( 3 dictFileName => '/database/V816/extproc/dictionary.ora'); 4 end; 5 / PL/SQL procedure successfully completed. SQL> select sql_redo from v$logmnr_contents 2 where seg_name = 'T2'; SQL_REDO ------------------------------------------------------------------------------insert into TEST.T2(COL1) values (1); insert into TEST.T2(COL1) values (2); update TEST.T2 set COL1 = 3 where ROWID = 'AAAGGUAAOAAA/8eAAA'; delete from TEST.T2 where COL1 = 2 and ROWID = 'AAAGGUAAOAAA/8eAAB'; SQL> -- UPDATE requires investigation to possibly form original predicate. SQL> select sql_redo, sql_undo from v$logmnr_contents 2* where seg_name = 'T2' and sql_redo like 'update%'; SQL_REDO ------------------------------------------------------------------------------SQL_UNDO ------------------------------------------------------------------------------update TEST.T2 set COL1 = 3 where ROWID = 'AAAGGUAAOAAA/8eAAA'; update TEST.T2 set COL1 = 1 where ROWID = 'AAAGGUAAOAAA/8eAAA'; Summary: The ROWID in v$logmnr.sql_redo/undo does NOT apply to the 'new' object. The mined DML references the 'new' object T2. This article does not address the various approaches to replaying the redo since each environment will dictate the requirements. INSERT: v$logmnr_contents.sql_redo for INSERT can be replayed as mined. DELETE: v$logmnr_contents.sql_redo for DELETE can be replayed by removing "and ROWID = ''" for the remainder of the predicate qualifies the original row. Unless the predicate contains a complete UNIQUE constraint, the predicate may not result a single row DELETE.
UPDATE: v$logmnr_contents.sql_redo for UPDATE can not be replayed without intervention. v$logmnr_contents.sql_undo will provide original row values, thus the 'set' clause of the sql_undo will be used to form the UPDATE predicate. In this extreemly simple example: update TEST.T2 set COL1 = 3 where COL1 = 1; But consider the following: SQL> create table t1 ( 2 col1 int primary key, 3 col2 int, 4 col3 int); SQL> insert into t1 values (1,1,1); SQL> insert into t1 values (2,2,1); SQL> update t1 set col3 = 3 where col1 = 1; Investigate v$logmnr_contents for UPDATE: sql_redo: update TEST.T1 set COL3 = 3 where ROWID = 'AAAGGdAAOAAA/8jAAB'; sql_undo: update TEST.T1 set COL3 = 1 where ROWID ='AAAGGdAAOAAA/8jAAB'; The following statement would NOT perform original single-row mutation: update t1 set col3 = 3 where col3 = 1; In this case, v$logmnr_contents can be used to determine 'old' values (sql_undo) and 'new' values (sql_redo) but it is not possible to construct UPDATE statement that resolves to correct row unless original statment updates (SET clause) all columns in a UNIQUE constraint. Thus, The redo records generated for UPDATE statements in Oracle8/8i uses ROWID to identify the updated row, therefore these statements can only be reconstructed under very restrictive circumstances. Oracle9i will provide the framework for UPDATE statement reconstruction in LogMiner without any ROWID references based on additional column data added to the Oracle9i redo log stream for UPDATE statements only. RELATED DOCUMENTS [NOTE:93370.1] How to locate SCN/Time of DROP TABLE using LogMiner [NOTE:74988.1] How to build an Oracle 8.0 LogMiner Dictionary LogMiner Analysis of DML on Chained Rows [NOTE:96197.1] RECOVERING A DROPPED TABLE FROM A FULL DATABASE BACKUP
Oracle 8.1.5 Administrator's Guide - Managing Archived Redo Logs Chapter Oracle 8.1.5 Supplied Packages Reference: dbms_logmnr_d, dbms_logmnr
17.8
cd /home/remedy/util ls Remedy_gpending.pl -g DBA-ORA Remedy_gpending.pl -g "DBA FI" Remedy_gpending.pl -g "DBA BOB" Remedy_mypending.pl
Accessing Remedy via Command line
When remedy is not functional via GUI, at times you could use command line :
These commands are faster than GUI, especially to close tickets.
17.9
Kerberos login notes
You need to login as your user, copy & paste the below lines for respective servers. cd /opt/krb5/bin ./kinit -A oracle p1ped0wn ./proxy-rlogin.pl njovmlmprd1 -l oracle -x cd /local/oracle/NJOVMLM1/backup nyovmlmprd2 cd /opt/krb5/bin ./kinit -A oracle p1ped0wn ./proxy-rlogin.pl nyovmlmprd2 -l oracle -x
18
Miscellaneous
18.1 Procedure to Update London Database Users Contact
Following is the procedure for adding or modifying the contact information for londba Oracle servers. 1 2 3 4 5 6 7 Identify the Server need to be modified or added. Identify the email id of the group /person to be added. Go to londba site. (londba on address bar). Click on contact admin , in DBA Only section. Click on server name, for which you want to change the contact info. Check the current primary contact. If no one defined, it will show as 'undefined', otherwise the name of group/ person will be displayed there. To change or add, click on the existing one (or undefined.).
8 9 10 11 12
Select the group/person from the drop down box. If the person/group not listed in the drop down box, then, step 9. If you could select the person, click on update Click on Help: The contact I want to add was not listed above. Add the details of the contacts. PNL is the profit-loss account code which you may have to check back the user. Always preferred to add a group. Submit the change and click update (same as in step 9). Refresh the page and see the change done.
Appendix A: Obsolete Parameters in 9i:
_average_dirties_half_life _lm_statistics allow_partial_sn_results always_anti_join always_semi_join arch_io_slaves b_tree_bitmap_plans backup_disk_io_slaves cache_size_threshold cleanup_rollback_entries close_cached_open_cursors compatible_no_recovery complex_view_merging cpu_count db_block_checkpoint_batch db_block_lru_extended_statisti db_block_lru_latches db_block_lru_statistics db_block_max_dirty_target db_file_simultaneous_writes delayed_logging_block_cleanout discrete_transactions_enabled distributed_lock_timeout distributed_recovery_connectio distributed_transactions fast_full_scan_enabled freeze_DB_for_fast_instance_re gc_defer_time
gc_latches gc_lck_procs gc_releasable_locks gc_rollback_locks hash_multiblock_io_count instance_nodeset job_queue_interval job_queue_keep_connections large_pool_min_alloc lgwr_io_slaves lm_locks lm_procs lm_procs lm_ress lock_sga_areas log_block_checksum log_files log_simultaneous_copies log_small_entry_max_size ogms_home ops_admin_group ops_interconnects optimizer_percent_parallel optimizer_search_limit parallel_default_max_instances parallel_min_message_pool parallel_server_idle_time parallel_transaction_resource_ push_join_predicate row_cache_cursors sequence_cache_entries sequence_cache_hash_buckets shared_pool_reserved_min_alloc snapshot_refresh_interval snapshot_refresh_keep_connecti snapshot_refresh_processes
sort_direct_writes sort_multiblock_read_count sort_read_fac sort_spacemap_size sort_write_buffer_size sort_write_buffers spin_count temporary_table_locks text_enable use_ism